My previous two posts about SSRS reports being deployed to a SharePoint site along with their subscription didn't stop me from exploring the whole things of SSRS reports subscriptions; regardless of their deployment environment either native or SharePoint mode.
After exploring different aspects of the subject (Subscriptions and Delivery) you'll become familiar with setting up delivery types, extensions and other metadata for your subscriptions (data-driven subscription brings even more flexibility :-). The only thing that puzzled me was how I could test each of the subscriptions: schedule, schedule and nothing but the schedule; and you even don't have a UI option to initiate or trigger subscriptions.
However, MS Reporting Service has a way to create an event that will trigger an SSRS report subscriptions no mater what those schedule definitions are. You can simply call a report server stored procedure dbo.AddEvent and provide two parameters: @EventType='TimedSubscription', @EventData=@Subsciption_ID (primary key from the dbo.Subscriptions table). And whatever action is defined in your subscription, it'll get performed!
There is another way to create the 'TimedSubscription' event within a report server, it's a FireEvent method of the ReportingServices web services that you can you call from anywhere. So let's be crazy enough and test this method from an SSIS Script Component that will trigger SSRS report subscriptions which are based in a SharePoint mode report server. Here is how it worked for me:
1) I created a proxy class from the ReportingServices web service (file location and SharePoint site will be different on your side)
C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\wsdl.exe /out:ReportService2010.cs http://yoursite.com/_vti_bin/ReportServer/ReportService2010.asmx
2) Then I compiled ReportService2010.cs into DLL:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe csc /target:library ReportService2010.cs
3) Signed this DLL file (Adding strong name for the library)
Details for this step are taken from here:
6) Diverted sourcing column with the Subscription IDs data to this component
7) Added reference to the ReportingService DLL in the script component
and executed my SSIS package! 🙂
After exploring different aspects of the subject (Subscriptions and Delivery) you'll become familiar with setting up delivery types, extensions and other metadata for your subscriptions (data-driven subscription brings even more flexibility :-). The only thing that puzzled me was how I could test each of the subscriptions: schedule, schedule and nothing but the schedule; and you even don't have a UI option to initiate or trigger subscriptions.
However, MS Reporting Service has a way to create an event that will trigger an SSRS report subscriptions no mater what those schedule definitions are. You can simply call a report server stored procedure dbo.AddEvent and provide two parameters: @EventType='TimedSubscription', @EventData=@Subsciption_ID (primary key from the dbo.Subscriptions table). And whatever action is defined in your subscription, it'll get performed!
There is another way to create the 'TimedSubscription' event within a report server, it's a FireEvent method of the ReportingServices web services that you can you call from anywhere. So let's be crazy enough and test this method from an SSIS Script Component that will trigger SSRS report subscriptions which are based in a SharePoint mode report server. Here is how it worked for me:
1) I created a proxy class from the ReportingServices web service (file location and SharePoint site will be different on your side)
C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\wsdl.exe /out:ReportService2010.cs http://yoursite.com/_vti_bin/ReportServer/ReportService2010.asmx
2) Then I compiled ReportService2010.cs into DLL:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe csc /target:library ReportService2010.cs
3) Signed this DLL file (Adding strong name for the library)
Details for this step are taken from here:
a) Generate a KeyFile
sn?-k?keyPair.snk
b) Get the MSIL for the assembly
ildasm ReportService2010.dll /out:ReportService2010.il
c) Build a new assembly from the MSIL output and your KeyFile
C:\Windows\Microsoft.NET\Framework\v2.0.50727\ilasm.exe ReportService2010.il /dll /key=keyPair.snk
d) Install the DLL in to the GAC
gacutil -i ReportService2010.dll
5) Created a Script Component task in my SSIS Data Flow task
6) Diverted sourcing column with the Subscription IDs data to this component
7) Added reference to the ReportingService DLL in the script component
and executed my SSIS package! 🙂
In a few moments I was able to see the results: emails were sent out and report files had been created; and I didn't even have to wait for schedules.
I hope that those 8 steps were detailed enough, otherwise let me know if you need more details on this.
Happy data adventures!
