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).