Post reply

Use SSIS package as datasource for Report

  • December 30, 2009 at 1:24 pm

    #228778

    I've configured SSRS to use a SSIS package as a datasource however I'm not clear on how to setup the datasource to use the package. When I go to create a shared datasource in a new SSRS project, I see "SSIS" as a type to select from. What goes in the connection string box? Both the edit and function buttons are disabled...? Do I just type in the name of the package or ....???

    Thanks in advance!

  • January 1, 2010 at 9:25 am

    #1098090

    To my knowledge, SSRS can only use TSQL statement i.e. a SELECT ... FROM ... or a stored procedure. So I don't think that this is possible.

    RAQ Report: Web-based Excel-like Java reporting tool[/url]

  • January 11, 2010 at 6:47 am

    #1101746

    It seems like it would be possible since you can configure SSRS to use SSIS as a datasource per Microsoft. I've done the configuration part in SSRS, now I'm just trying setup a report to pull from the SSIS package.

  • January 12, 2010 at 1:00 pm

    #1102693

    Yes, you can do it. Little tricky though. This is the best source I've found for how to set it up

    http://blogs.msdn.com/bimusings/archive/2006/07/19/671743.aspx

    I've also used straight sql with a dtexec statement to execute the package from the report.

  • January 14, 2010 at 8:39 am

    #1103653

    Karen L (1/12/2010)

    Yes, you can do it. Little tricky though. This is the best source I've found for how to set it up

    http://blogs.msdn.com/bimusings/archive/2006/07/19/671743.aspx

    I've also used straight sql with a dtexec statement to execute the package from the report.

    Karen,

    WOW! Thanks for the link. That has really helped me make some progress. I have successfully created a SSRS report that pulls from a SSIS package. I've run into an issue where the report runs without any problem in BIDS however, once I deploy the report to the SSRS server, I then get a "The package failed to validate. " error for the datasource. I have a valid execution account setup in the SSRS config yet I'm not sure where the problem actually is.... Have you run across this error and know what potentially would be it's cause?

    The error displayed from SSRS is:

    An error has occurred during report processing.

    Query execution failed for data set 'DataSet1'.

    The package failed to execute.

  • January 14, 2010 at 2:53 pm

    #1103976

    have you looked in the reprort server logs to find the specific error? My guess is it has something to do with permissions.

  • January 15, 2010 at 12:59 pm

    #1104414

    I turned on some logging for the SSIS package and was able to determine that it was a permissions type error. In the SSIS package I was writing the dataset results to a text file in addition to a dataset destination object. The drop location for the text file didn't have all the correct folder permissions for the report server to access so all I had to do was update the folder permissions as needed. At one point I thought that I was going to need to change the SSRS Windows Service account to a domain account as it is currently just set to a local system account but it doesn't look as if that is necessary. That may happen later for other reasons...

    So the issue is resolved. Hope this thread is useful to others who need to create reports from SSIS packages.

Viewing 7 posts - 1 through 6 (of 6 total)

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