SSRS #47 – Manage subscriptions dynamically
In my previous post, SSRS #43 – Problem with undocumented SQL procedure AddEvent when kicking off a Timed Subscription, I mentioned that the report link from the Subscription is not a valid link. Our architecture team informed me that using undocumented procedure AddEvent probably is not the best way to kick start a Reporting Services Subscription. The recommendation is to use the exposed web services provided by the reporting Services within my SSIS package to kick start the Subscription.
Well so far I couldn’t get it to work because I couldn’t even get the ReportingService class to work in either a Script Task or the Web Service Task. There are many articles talking about how to get the ReportingService class reference to 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 locally
According to this article on MSDN, Web Services Description Language Tool (Wsdl.exe), the file wsdl.exe is part of the.NET Framework 2.0 Software Development Kit. Once installed, the tool (wsdl.exe) can be found in the folder: C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. Since I don’t have the SDK Kit installed, no solution for me so far. I don’t want to install it right now either, since this is not on my priority list.
My priority regarding managing Subscriptions on Reporting Services
What is on my priority list:
1) Continue to use the AddEvent procedure to kick start the Subscription, but do not hard code the SubscriptionID. This should be easy to achieve. See below.
2) I need to switch from Subscription to Data Driven Subscription.
Reasons for the above priority
If you have created a Timed Subscription on Reporting Services, you must have noticed how “easy” it is to select values for your report parameters. You must have also noticed a few times when the automatic emails did not fire off. You scratched your head, and couldn’t figure out why. Later you will find out that it’s because the values you picked on the Subscription are no longer valid, or you have added more report parameters, or have deleted a few parameters. In another word, the Subscription you have created is no longer valid.
Retrieve SubscriptionID Dynamically
I need to do the first thing on the list if I am going to continue to use the Subscription, instead of the Data Driven Subscription, so at least I have a valid SubscriptionID if I changed something on the Subscription.
This is a relatively easy task. I would create two Execute SQL Tasks, and a package variable varSubscriptionID.
The variable varSubscriptionID will be populated in the first Execute SQL Tasks with a query like this:
In the second Execute SQL Tasks , I will use this to kick start the Subscription by passing the variable varSubscriptionID to it (represented by the question mark).
Data Driven Subscription
There are many articles about why we need to use Data Driven Subscription VS. just the plan Subscription extension on Reporting Services. Most bloggers have done a great job. I don’t need to report them here. To me, I need to use it because I do not want to hard code the values for my report parameters. I found out what would happen if the values are no longer valid. I also want to be able to customize the subscriptions for different business groups. The idea is the same as my first priority, I want to manage the subscriptions in a dynamic way.
I will blog about the Data Driven Subscription in the future.