Run and Export SSRS Reports from SSIS (SQL Server 2005) May 19, 2009
Posted by Zoran Ivić in SSIS.trackback
Few days before I finished SSIS package which produces lot of Excel sheets for regular use in company where I work. I am not so sure how much is useful to have sheet with 20 or 30 thousands rows (yes, that’s for regular work), but I made a tool and Server takes care about populating Excels.
So, customers are almost happy, but… as an addition to excels I had to made Summaries for every excel. I decide to made SSRS report with one parameter and run it trough SSIS and export it like PDF file in same folder where Excels are. Unfortunately, wasn’t so easy to find simple solution and that is reason for this post…
Picture of simplified solution:
Execute SQL Task – collection of “Parameters” from database
Foreach Loop Container – Loop execution of report for every parameter from Sql Task (trough AdoEnumerator) saved in string variable
Script Task – render report in pdf and save it on local disk
Script task needs Credentials for HttpWebRequest class. I’m using default, but it can be changed in SSIS configuration file.
Example package can be downloaded here ReportExport.zip – delete .doc extension (SSIS and SSRS project with sample report).
Thank, work very well.
Thanks man! It\’s a really cool solution for client who bought SQL Server STANDARD edition instead of Enterprize,
Thank a lot for sharing..
I am not able to open the downloaded file..
Please delete .doc extension.
This is genius… been trying to figure this one out for a while now!
I’m glad I could help.
can this same thing be done with preinter as a delivery extension. I have been tryint to deploy the printer delivery extension sample for two days and my data driven subscription is not able to find the printer on the server and prints as .xps file on disk.
I am thinking how can i modify the script to print the pdf generated to a specific netwrk printer .
Please suggest.