Share via

Firing a Reporting Services Subscription from an Integration Services Package

  • Article

I am working with a customer who needs to trigger the subscriptions associated with a Reporting Services report just after the SSIS package responsible for refreshing the data behind the report completes its run. Since the customer is already working with SSIS, I figured using a Script task to call the Reporting Services web service’s FireEvent method might be the easiest way to go about this. The following are the high-level steps I performed to demonstrate this approach to the customer.

Note For the purposes of the demonstration, I am working with Reporting Services 2008 SP1 running in Native mode on my local system. In Reporting Services, I am a member of the System Administrator server role and a Content Manager in the Home directory.

The first step is to create a subscription associated with a report. This subscription should never run on its own so you’ll assign it a run-once schedule set to end on a date in the past.

In my demo, I’m using a report-specific schedule. I’ve read a blog suggesting the use of a shared schedule but my concern is that when I fire the event in my code, any subscription tied to that schedule will run. You can use either type of schedule just be aware of the consequences.

1. Open Report Manager and locate the report. In my demonstration, this is a report called Blank Report in the Demo Reports folder located under Home.

2. Setup subscription with a subscription-specific schedule set to run once with a start and end date set to a day in the past.

Next, you need to assign the permissions required to call the FireEvent method. In my local instance of Reporting Services, I am a member of the System Administrator server role but even this role doesn’t have the required permission by default.

To assign the permission you have two options, you can modify an existing server role or create a new one. To keep my system standard, I created a new one with just that permission and then assign myself membership in that new server role:

1. Open SQL Server Management Studio and connect to your instance of Reporting Services.

2. Expand the Security folder, right-click the Server Roles child folder, and select New Server Role to launch the New Server Role dialog.

3. Assign the role an appropriate name (and description) and select the Generate events task.

4. Click OK to create the role and close the dialog.

5. Return to Report Manager.

6. Open Site Settings and then the Security page.

7. Assign yourself membership in your new server role.

With the subscription and security in place, now it’s time to start programming. As I said earlier, the customer’s goal is to tie this all into an ETL layer that’s written using SQL Server Integration Services. So, for this demonstration, I create a package, add a script task, and enter the required code:

1. Launch the SQL Server Business Intelligence Development Studio and create a new Integration Services package.

2. Drag a Script task from the toolbar to the control flow.

3. Open the Script task, select an appropriate language—VB.NET in my example—and then click the Edit Script… button to launch the Visual Studio Tools for Applications environment.

4. From the Project menu, select Add Web Reference to launch the Add Web Reference dialog.

5. For the URL, enter the path to Reporting Services web service’s ReportService2005 endpoint. On my local system, this is https://localhost/reportserver/reportservice2005.asmx?wsdl.

6. Clicking Go retrieves the metadata for the web service endpoint which populates the dialog. Modify the web reference name as appropriate – I chose rs for my web reference name—and then click Add Reference to add the reference to your project.

7. With the web reference in place, you can now enter your code. For my demonstration, I modified Sub Main as follows:

   Public Sub Main()

     Dim MyRs As New rs.ReportingService2005()

     MyRs.Credentials = System.Net.CredentialCache.DefaultCredentials

     Dim MySubs As rs.Subscription() = _

      MyRs.ListSubscriptions("/Demo Reports/Blank Report", Nothing)

     Dim MySub As rs.Subscription = MySubs(0)

     MyRs.FireEvent(MySub.EventType, MySub.SubscriptionID)

   Dts.TaskResult = ScriptResults.Success

   End Sub

 

Let me pick apart this code a bit. I first declare a new object, MyRs, which represents the web service interface. I establish my identity with the interface leveraging my active Windows credentials.

 Then I open the collection of subscriptions associated with my report, /Demo Reports/Blank Report. I then grab the one subscription tied to my report and fire the event associated with it before completing the routine.

So, how do I know the subscription I want is the one with an index of 0 in the subscriptions collection? Well, in my example, there is only one subscription. In the real world, you’ll need to look at the subscription’s properties or some other mechanism to determine which one to fire.