Error when using SSIS as datasource

[SQLHacker]

Using SQL Server 2005. I created an SSIS package that runs fine by itself and provides an ADO.NET recordset as output (Data Reader Destination). I then created an SSRS report that connects to that SSIS package as its datasource. On the Data tab in the SSRS designer, I can execute the query, and all the expected data is returned. Also, when I preview the report in the designer, all the expected information appears.

However, when I deploy the report to the ReportServer, and try to run it there, I get the below error.

An error has occurred during report processing.
Query execution failed for data set 'DataSet_MergedJobsAndTasks'.
The package failed to execute.

Any suggestions would be much appreciated.

 

[MDXer]

I know within SSIS that the ADO.NET Record set is an in memory recordset whose structure is unknown at runtime, this is the reason it has a destination but no source. Giving the fact that the Recordset is created at the time of the output it also means it is destroyed upon completion of the package. So I do not think what you are trying to do is even possible.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

[URL unfurl="true"]http://msdn.microsoft.com/en-us/library/ms345250.aspx[/url]

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[SQLHacker]

Paul,

Thanks for the response. I don't think I've ever tried this before, so you may be right. The thing that really puzzles me is why it does work in the designer environment but not in the web-rendering environment. If the problem is that it's not getting a dataset at runtime, it should fail in the designer too...right?

 

[MDXer]

You would think but I like you have never done it.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck