Consuming the Reporting Services web service inside SSIS

I had a request to go into more detail as to an implementation of the ReportService web service inside SSIS.  I decided I’d take a crack at it.

Taking a snapshot of a report after a successful ETL load is a common task.  You could automate this task into the ETL load by consuming the Reporting Services web service and calling the UpdateReportExecutionSnapshot method and invoking Reporting Service to take a snapshot.  This could then be a catalyst for subsequent subscriptions for users who have asked to be notified when a new version of a report is ready.

First of all, we need to create a proxy object for the service by extracting the WSDL document from the service and using it as an input into a tool that Microsoft provides with Visual Studio called wsdl.exe.  The most straight forward execution for wsdl.exe would look something like this:

wsdl.exe /language:VB /out:ReportService.vb http://%5BReportServerName]/ReportServer/ReportService.asmx?WSDL

OR

wsdl.exe /language:CS /out:ReportService.cs http://%5BReportServerName]/ReportServer/ReportService.asmx?WSDL

You can choose your output language according to your liking, VB or C#.  Complete documentation of all the options for invoking wsdl.exe can be found here.

Once the proxy class’ code has been generated, it is time to get that code imported into an SSIS package.  Go open an SSIS package and place a Script Task into the Control Flow area.  Once that is completed, double click on the new Script Task to open it up.  Next go to the Script “tab” over on the left and then click Design Script… on the right side.  We are now in the code editor for the Script Task.

Next we need to add a new code file into the task.  To do this, go to the top level of the tree view in the Project Explorer (mine is on the left side of the Microsoft Visual Studio for Applications window) and right click.  This will bring up a context sensitive menu.  Choose Add, and Add Class….  When the next dialog box comes up, name the file ReportService.  After clicking the Add button, there should be a new item in your Project Explorer tree view called ReportService.

There will be a little bit of code auto generated by SSIS inside the new file.  We’ll go into the file by double clicking on it and delete out the auto generated code.  Next, go to the file that was created by wsdl.exe and open it up in notepad.  We just need to select all the code in it and paste it into the new file inside the SSIS task we just created.  The code editor should now look something like this:

We need to add a couple of references inside the script task as well.  Right click on References in the Project Explorer and click Add Reference….  Then choose System.XML and System.Web.Services and click OK.  Now we are ready to program against our proxy object.  To begin using the new class we created we need to instantiate it into an object.

You can see here that after I instantiate the object, I’m able to view its methods (known as web methods) using IntelliSense.  For every operation that ReportService.asmx exposes, there are two versions.  One is an asynchronous version and a synchronous version.  The asynchronous version allows you to make the call out to begin the web method call and be alerted when the call is finished by using a callback.  This allows the calling process to keep from blocking its main thread during the call.  These methods work in a pair and have Begin[MethodName] and an End[MethodName] procedure.  The synchronous method blocks the calling thread during execution and is just named [MethodName].  We’ll use the synchronous method here.  You can find more information on using asynchronous web method calls here.

The method we will concentrate on here is the UpdateReportExecutionSnapshot method.  Reports in Reporting Services can be rendered from a snapshot that is taken at an earlier time so that no database query is needed and report response is increased.  One way to take a new snapshot is to declare a schedule and have the snapshot taken at a particular time.  This is sometimes not practical since ETL completion time can vary from day to day.  We can change the properties of a report to render from snapshot and then create a new snapshot after ETL processes are complete.

Before the UpdateReportExecutionSnapshot method will work, security must be set up properly.  You can either change the ReportServer directory to allow anonymous access (not recommended) or apply the credentials of the caller to the web service call.  This is done by using the System.Net.CredentialCache namespace and assigning the Credentials property of our proxy object to be equal to System.Net.CredentialCache.DefaultCredentials.

To make the complete call and create a new snapshot, here is the code:

Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.UpdateReportExecutionSnapshot(“[ReportName]”)

ReportName is a string that describes the full path to the report.  If a report called “Test Report” were in the root or home directory, you would just need a forward slash then the report name – “/Test Report”.  If the same report were in a directory called “foo” then the ReportName string would look like this: “/foo/”Test Report”.

Hopefully this example will help everyone use this method for interacting with Reporting Services at ETL time.

Good Luck!

Advertisements

18 Responses to Consuming the Reporting Services web service inside SSIS

  1. sam greene says:

    Great article, exactly what I was looking for. During execution I’m getting the error below. Can’t find any info on this error in google. I’ve made sure the “Allow report history to be created manually ” is checked, but i’m not sure if that allows access through the web service – maybe the admin has it locked down? Thanks for any ideas you might have!
    Sam

    Report execution or history settings prohibit snapshot creation or updates. —> Report execution or history settings prohibit snapshot creation or updates. —> Report execution or history settings prohibit snapshot creation or updates.

    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at ScriptTask_a01075d7429b44ccbdeec7914f39a6da.ReportingService.UpdateReportExecutionSnapshot(String Report)
    at ScriptTask_a01075d7429b44ccbdeec7914f39a6da.ScriptMain.Main()

  2. Mark Garner says:

    I can’t think of anything right off hand. Let me give it a think and I’ll let you know.

  3. sam greene says:

    I changed my code to use these methods:
    rs.SetReportHistoryOptions(“/SQL_DBA/SQL_DBA_SSIS_TI_REPORT”, True, True, Sched)
    ReturnCode = rs.CreateReportHistorySnapshot(“/SQL_DBA/SQL_DBA_SSIS_TI_REPORT”, warnings)

    I don’t think the first line needs to be run every time.

    I am using the return code to email the report url to a user, like this:

    ReportURL = “https://myRSServer/Reports/Pages/Report.aspx” & “?ItemPath=%2fSQL_DBA%2fSQL_DBA_SSIS_TI_REPORT&HistoryID=” & ReturnCode

    Dts.Variables(“User::ReportURL”).Value = ReportURL

  4. sam g says:

    Looks like I was not clear on the diff between Report History and Report Execution Snapshot. Report Execution being a cached data for the report. Report history is what I was after, having multiple versions available for users to see. Thanks again for the article.

  5. Mark Meed says:

    This was enormously helpful. Thank you.

  6. DC says:

    Dim rs As New ReportingService()
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials

    How do you set the Credentials when you have Form Authentication?

  7. JL says:

    Could Microsoft have made this anymore complicated? For a DBA that doesnt know VB or C# this too much work when you just want to send a nicely formatted report when a package finishes.

  8. Jeff says:

    Hi Mark:

    Thanks for this excellent writeup. Sorry to hear you’re dealing with the floods. Hope everything works out OK for you. (And a belated happy birthday, too.)

    I’m going to use this to compare reports of production vs. test data warehouses, to make sure they both return the same data. I plan to use SSRS execution log as basis for SSIS data flow comprising actual user report requests and the parameters used. Probably will render to XML, and then compare using an XmlDiff class…

    Thank you again. -j

  9. Cyborg says:

    nice article… 🙂
    It was of a good help to me…
    thanks again

  10. Pete says:

    Great article. Helped us out immensely last night. Awesome step by step tutorial… it walked us right through perfectly.

  11. Fbuy says:

    I really appreciate what you’re doing here.

  12. ck says:

    Good article. Thanks!

  13. […] an alternative — you'll find many — for invoking RS inside SSIS, and this one uses SOAP.  I think this is a […]

  14. Owen Landi says:

    Greetings I recently finished reading through your blog and I’m very impressed. I do have a couple queries for you personally however. You think you’re thinking about doing a follow-up putting up about this? Will you be going to keep bringing up-to-date as well?

  15. […] work in a SSIS package. This one seems to have a very detailed instruction on how to make it work, Consuming the Reporting Services web service inside SSIS. Unfortunately so far it’s not working for me because I could not find the wsdl.exe file […]

  16. […] work in a SSIS package. This one seems to have a very detailed instruction on how to make it work, Consuming the Reporting Services web service inside SSIS. Unfortunately so far it’s not working for me because I could not find the wsdl.exe file […]

  17. lionel messi naked…

    […]Consuming the Reporting Services web service inside SSIS « The Furnace[…]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: