Using SSIS as a datasource for Reporting Services

I have SQL Server 2008 SP1 (64 bit) running

• SQL Server
• Integration Services
• Reporting Services

on a Windows 2003 Server (64bit).

I'm trying to get Reporting Services to use an integration services package as a datasource. I've created a very simple package that simple reads a text file and loads it into a DataReaderDestination.

I have edited the rsreportserver.config file as documented here: http://msdn.microsoft.com/en-us/library/ms345250.aspx

Now I can design and sucessfully preview a report in Business Intelligence Design Studio on a remote machine. However when I publish the report (and copy over the SSIS package and the source file to the server), when I attempt to run the report I get:

An error occurred during client rendering.
An error has occurred during report processing.
Query execution failed for dataset 'DataSet1'.
The package failed to execute.

If I switch on logging. It is clear that the package gets called but is failing:

OnPreValidate,LLPEDIA014,LLPDEV\testuser,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:30,22/04/2009 14:22:30,0,0x,(null)
OnPreValidate,LLPEDIA014,LLPDEV\testuser,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:30,22/04/2009 14:22:30,0,0x,(null)
PackageStart,LLPEDIA014,LLPDEV\testuser,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:30,22/04/2009 14:22:30,0,0x,Beginning of package execution.

Diagnostic,LLPEDIA014,LLPDEV\testuser,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:30,22/04/2009 14:22:30,0,0x,Based on the system configuration, the maximum concurrent executables are set to 4.

OnPreExecute,LLPEDIA014,LLPDEV\sqlAdmin,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:30,22/04/2009 14:22:30,0,0x,(null)
OnError,LLPEDIA014,LLPDEV\sqlAdmin,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:30,22/04/2009 14:22:30,-1073450954,0x,component "DataReaderDest" (1) failed initialization and returned error code 0x8007000E.

OnError,LLPEDIA014,LLPDEV\sqlAdmin,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:31,22/04/2009 14:22:31,-1073594105,0x,There were errors during task validation.

OnWarning,LLPEDIA014,LLPDEV\sqlAdmin,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:31,22/04/2009 14:22:31,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

OnPostExecute,LLPEDIA014,LLPDEV\sqlAdmin,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:31,22/04/2009 14:22:31,0,0x,(null)
PackageEnd,LLPEDIA014,LLPDEV\testuser,TestPackage2,{3449E63D-6CF5-416A-9E16-B02F78994AFB},{227463EE-5D0C-4871-A9A6-5EFE7DFF48FD},22/04/2009 14:22:31,22/04/2009 14:22:31,1,0x,End of package execution.

Has anyone experiencied similar problems or got this to work? Thanks.

This is a bit of a shot in the dark, but under which service account is Reporting Services running?

When used as a data source, the SSIS package executes under the security context of the SSRS service. Since the package is so simple and it is failing during validation, it suggests that something fairly basic is wrong.

Reporting Services installations can optionally be configured with a low-security execution account under which reports are run. When this is set up, I'm not sure whether the SSIS package will be run under the service account or the execution account.

A security issue would also account for the package running successfully in the designer (under your security context), but not when deployed.

The easiest way to test this will be to change the SSRS service account (and execution account, if configured) to one under which the SSIS package is known to run - like your own. If that's not possible, you may need to look at granting permissions on the folders where the source text file is held to the service/execution account.