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!