Thru SSIS execute SSRS by passing parameters

[Pramod Jamadagni]

URL: http://msbiblog.wordpress.com/2010/09/24/run-and-export-ssrs-reports-from-ssis-sql-server-2008/

 

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.

 

 

Thanks,

---

Pramod Jamadagni,

Indecomm global services - Technology division,

#10, Brigade south parade,

MG Road, Bangalore - 560001.