Post reply

Generate SSRS reports from a SSIS Package

  • October 8, 2012 at 11:26 pm

    #282810

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

  • October 9, 2012 at 1:19 am

    #1547037

    Hi ,

    Is it possible to pass parameters to the report ?

  • October 9, 2012 at 1:33 am

    #1547044

    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

  • October 9, 2012 at 3:22 am

    #1547092

    Great post. Very helpful!!!

  • October 9, 2012 at 3:23 am

    #1547093

    Very Good Article ...

    Really Useful !!!!

  • October 9, 2012 at 6:26 am

    #1547190

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

  • October 9, 2012 at 7:47 am

    #1547221

    Very cool. Thanks!

  • October 9, 2012 at 10:26 am

    #1547285

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

  • October 9, 2012 at 11:19 am

    #1547308

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

  • October 9, 2012 at 11:38 am

    #1547317

    Hi,

    on Codeplex you can find my SSIS ReportGenerator Task (http://reportgeneratortask.codeplex.com/).

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

    Cheers,

    Tillmann

  • October 9, 2012 at 1:28 pm

    #1547352

    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.

    MWise

  • October 9, 2012 at 5:20 pm

    #1547413

    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

  • October 11, 2012 at 2:05 pm

    #1548342

    There is also an MSDN article on generating reports in large volumes, using SQL SSIS and SSRS 2008 R2 -- http://msdn.microsoft.com/en-us/library/ff793463(SQL.105).aspx

  • October 11, 2012 at 3:24 pm

    #1548370

    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.

  • October 11, 2012 at 7:51 pm

    #1548413

    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