Can I loop through table and run reports from SSIS

I would like to loop through a SQL Server table that contains the paths to all the reports we need to run and then execute the reports via SSIS. What task should I be doing to do this? Will the For Loop work for something like this?Yes, you can use the ForEach loop to iterate through the resultset of a query.

So you would use an Execute SQL task to select your report URLs and place them in an object variable. You can iterate through those resultset rows in a ForEach ADO Enumerator loop and place values of the columns into variables. Then inside the loop you would use a script task to make a WebRequest to SSRS with a URL you constructed from the variables. You can then write the stream from the WebResponse to a file.

Hopefully you are planning to render these reports to a binary format such as Excel or PDF instead of HTML. HTML reports generally comprise multiple HTTP streams and cannot be easily executed without a browser.
|||

Actually I am running Cognos reports so I would need to build the cmd line command and execute an executable from a command prompt.

Any example of the above on the web?

|||You can run an executable through the Execute Process Task.|||

agentf1 wrote:

Actually I am running Cognos reports so I would need to build the cmd line command and execute an executable from a command prompt.

Any example of the above on the web?

Oh, I shouldn't have assumed SSRS. Running from the command prompt makes it easier. That changes the above so that you'd have another variable that is expression-based to construct your command line, then use an Execute Process instead of script. No code at all.

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/03/19/SSIS_3A00_-Evaluating-variables-as-expressions.aspx

|||

Hi JayH

My Name is Praveen . Could you please explain how it could be done in detail . It would be very helpful if send an answer to it .Especially the scripting part i didnt understand anything

|||I would like to loop through a SQL Server table that contains the paths to all the reports we need to run and then execute the reports(SSRS) via SSIS. What task should I be doing to do this? Could you explain in detail