Generate SSRS reports from a SSIS Package

  • Comments posted to this topic are about the item Generate SSRS reports from a SSIS Package

  • Hi ,

    Is it possible to pass parameters to the report ?

  • Yes, you can use SetExecutionParameters method in ReportExecutionService to set the parameters before rendering the report. This can be done in the code in Script Component. Here is the documentation for this method: ReportExecutionService.SetExecutionParameters

  • Great post. Very helpful!!!

  • Very Good Article ...

    Really Useful !!!!

  • I have been wanting to be able to do this for years. Thanks a lot!

  • Very cool. Thanks!

  • good stuff. What is the advantage of doing this versus firing a subscription for the report?

  • "What is the advantage of doing this versus firing a subscription for the report?"

    You can email a report snapshot to interested parties as soon as new data has been added to the underlying table.

  • Hi,

    on Codeplex you can find my SSIS ReportGenerator Task (

    It's not an "out of box" task but the task does exactly what you do with the script component.



  • If you are doing data driven subscriptions, then you have to have Enterprise edition. You can fire a subscription to run right after the package finishes. IIRC you just execute SQL to fire the event for the subscription. However, I have found that if you have several reports/recipients in the subscription the subscription may fail and report no error back to the calling job. You'll just see the number of errors in the subscription list in Report Manager, in some cases you won't see any errors and you'll have to crack open the report services log to find out what went wrong. So via subscription, there is no way to handle the errors (you'll just get a user calling you up asking why they didn't receive their report), with the SSIS script task, you can include all the error handling that you want.


  • Another method I have used is saving the report as a subscription which creates an agent jobs. you can either run that agent job after that or copy the data from the agent and execute in tsql command

  • There is also an MSDN article on generating reports in large volumes, using SQL SSIS and SSRS 2008 R2 --

  • Thanks for posting this article. I've got the report generated, but I need to send it as an attachment to the ETL completion email. However, it appears that the file generated by the report execution call remains locked until the full SSIS package completes. Do you have any tips on overcoming this issue? I've tried adding writer.close();, writer.dispose(); and rs.dispose(); lines to the end of the try block, but the file still remains locked when the send mail task executes.

  • Could you make a copy of the file and email that?

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply