Execute SSRS report from SSIS package Part#2
Posted by Sunil Reddy Enugala on October 16, 2013
Hi, Earlier we have discussed on execution of SSRS Report from SSIS package. I hope it reached most of the people as it shows in the stats of my blog.
If you have goon through my last post on Standard Subscription, I have shared a script which gets list of subscriptions created on that server.
Please find that same script below again.
If you run the above query, the result would look like below figure.
I am using Data Driven Subscription which I have created/discussed in my last post, as I can pass Parameter values to the report.
As we discussed, If you create Subscriptions in SSRS, a SQL Server Job will be created in the given instance of SQL Server to
Execute the report on scheduled time.
Please go through the below figure, where you can find the SQL Server Jobs with the names resulted from the above Query.
These Subscription Jobs will execute my EmpDeptReport Report, for the given scheduled time while creating the subscriptions.
In my last post on Data Driven Subscription I have created two SQL Server tables, to pass the Input parameter values to both
E-Mail and FileShare type of Data Driven Subscriptions. Please go through the below figure to find the Schema.
We can use above SQL Jobs and tables to execute the EmpDeptReport Report, from a Stored Procedure / SSIS Package.
Please find the below script to execute a SQL Job.
I have changed the Report parameter value in the Subscription input table and used the above script to deliver EmpDeptReport Report into a FileShare path.
Please go through the below figure, as soon as I executed the Subscription Job, EmpDeptReport Report was rendered in Excel format into the Shared path folder.
Similarly you can use the E-Mail Data Driven Subscription Job to deliver the report for given Report parameter value.
Please send me the issues/queries you have while following the above approach.
All the Best..
This entry was posted on October 16, 2013 at 7:30 am and is filed under MS BI Related. Tagged: call SSRS Report from SQL Server, call SSRS Report from SSIS, call SSRS Report from Stored Procedure, Calling SSRS Report in SSIS Package, Execute SSRS Report from Procedure, Execute SSRS Report from SQL Server, Execute SSRS Report from SSIS, Execute SSRS Report from Stored Procedure. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.