Use SSIS package as datasource for Report

Question

Use SSIS package as datasource for Report

C2S

SSC-Addicted

Points: 499
More actions
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!

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

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

becklery Hall of Fame Points: 3315 More actions · Answer 1

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]

C2S SSC-Addicted Points: 499 More actions · Answer 2

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.

Karen L SSC-Addicted Points: 427 More actions · Answer 3

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.

C2S SSC-Addicted Points: 499 More actions · Answer 4

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.

Karen L SSC-Addicted Points: 427 More actions · Answer 5

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

C2S SSC-Addicted Points: 499 More actions · Answer 6

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.