I have read numerous threads, articles, etc. about this and have been able to create an SSRS report using an SSIS data source. I'm just not able to run it, even on my own desktop. SSIS DataReaderDest reads data from one small table on a SQL Server 2005 database on a remote server. In SSIS I can read the results through a Data Viewer. Using an SSIS data source type, SSRS sees the four columns from the table and allows me to create the report. I have tried both No credentials and Windows credentials with the same results. When I attempt to preview the report, it executes for a few seconds and then returns: An error occurred during local report processing.An error has occurred during report processing.Query execution failed for data set 'SSIS_pcm'.The package failed to execute. There are no errors in the Visual Studio Error List. My guess is something isn't accessing the remote server, but then since I have zero experience with this, it is only a guess. I am going to attempt using a local data source, but would like to understand what's going on so that I can eventually get this to work. Any idea why I am experiencing this error?

Hi, Yes, the generic reason for this kind of errors is either you do not have sifficient permissions to access the remote server or the remote server doesn't set the "Allow remote connections to this server". Check the following things 1. From Management Studio, go tot he server properties ans checkk the "Allow remote connections to this server". 2. From SQL Server Configuration Manager , check the TCP/IP enabled for SQL Server Network configuration and Native Client configuration 3.From the SQL Server Surface area configuration,check the Database engine, Remote connections and set the Local and Remote connections to be true (Default is only Local connections) Hope this helps. -Lakshmi

I appreciate your assistance Lakshmi. The SQL Server database allows remote connections. I can access data from my desktop usingconnections from SSIS and SSRS. What doesn't work is using the SSIS package within SSRS. As I mentioned, the column names are recognized and the report builds. The error occurs upon preview. I get the very same results using an ODBC data source and an Excel spreadsheet on my desktop. This doesn't involve any remote server at all. I am also unable to read the data under the "Data" tab in Visual Studio. The error message doesn't mean anything to me. =================================== An error occurred while executing the query.The package failed to execute. (Microsoft Report Designer) =================================== The package failed to execute. (Microsoft.SqlServer.Dts.DtsClient) ------------------------------Program Location: at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread() at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.ExecuteQuery() My guess is I am missing something in the manner in which I am setting up the report or the SSIS data reader? Is there a process that should be running to accomodate this?

Getting exactly the same error here. Log very unhelpful and there seems to be very little information out there on this - if I run under BIDS no problem, data comes through, delpoyed on server I get the generic "System.Exception: The package failed to execute" error - likewise in the event viewer. That's all. I have logging on the package - which gets written to fine when I run through BIDS, however it doesn't even seem to get that far when the reportserver tries to execute the package. My guess is that this is some kind of permissions issue when the reportserver is trying to execute the package. Very keen to hear anything from anyone about this.

UPDATE! I got it to work, but don't necessarily like the solution. I found login errors in the SQL Server log for the 'NT AUTHORITY\NETWORK SERVICE'. So, to see if it would work, I added the permissions... sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE' and granted access to the DB the package accesses. This allowed it to run. What I don't understand is why the unattended execution account permissions were not used. Is there a way to change this behavior so I can impersonate some other user? ------------------------------ I checked all the security settings, and they are all as Lakshami indicated they should be. NOTE, before I set the Execution Account, I got a permissions error. After I setup a execution account (which I setup as a Domain Administrator for testing so permissions would not be an issue), then I started to get the Query execution failed for data set 'XXXX''. ---> System.Exception: The package failed to execute. Any other settings we need to set to make this work? The report works totally fine within BIDs, its only when trying to run it from SSRS (and note that SSRS and SSIS are on the same machine, and the data source is set to Localhost). Here is the log: w3wp!library!7!10/13/2008-21:08:57:: i INFO: Call to RenderFirst( '/Reports/TestReport' )w3wp!extensionfactory!7!10/13/2008-21:08:57:: i INFO: A wrapper has been created for the connection to the SSIS data source.w3wp!processing!7!10/13/2008-21:08:58:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestQuery'., ;Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestQuery'. ---> System.Exception: The package failed to execute. at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread() at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery() --- End of inner exception stack trace ---w3wp!processing!7!10/13/2008-21:08:58:: e ERROR: An exception has occurred in data source 'TestQuery'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestQuery'. ---> System.Exception: The package failed to execute. at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread() at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery() --- End of inner exception stack trace --- at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeReportDataSetNode.FirstPassInit() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.FirstPassProcess(Boolean& closeConnWhenFinish) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeReportDataSetNode.Process() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.ProcessConcurrent(Object threadSet)w3wp!processing!7!10/13/2008-21:08:58:: i INFO: Merge abort handler called for ID=13. Aborting data sources ...w3wp!dataextension!7!10/13/2008-21:08:58:: w WARN: CommandWrapper.Cancel not called, connection is not validw3wp!processing!7!10/13/2008-21:08:58:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestQuery'. ---> System.Exception: The package failed to execute. at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread() at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery() --- End of inner exception stack trace --- at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeReportDataSetNode.FirstPassInit() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.FirstPassProcess(Boolean& closeConnWhenFinish) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeReportDataSetNode.Process() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.ProcessConcurrent(Object threadSet) --- End of inner exception stack trace ---w3wp!processing!7!10/13/2008-21:08:58:: w WARN: Data source 'TestDataSource': Report processing has been aborted.w3wp!processing!7!10/13/2008-21:08:58:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestQuery'. ---> System.Exception: The package failed to execute. at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread() at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery() --- End of inner exception stack trace --- at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeReportDataSetNode.FirstPassInit() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.FirstPassProcess(Boolean& closeConnWhenFinish) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeReportDataSetNode.Process() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.ProcessConcurrent(Object threadSet) --- End of inner exception stack trace ---

I have exactly the same problem as a--dn--. This was working in SQL Server 2005 but in 2008 I'm having problems. Report runs fine in BIDS, but when I try to run it in report server i get the "package failed to execute" error. I don't get any login errors in SQL Server and I'm using the same account as execution account that I used to create the SSIS package. I've tried this in 3 different machines, 2 XP and 2003 Server, always the same error. Report server log doesn't help either. I've tried almost every possible combination in configuration settings.I'm very dissapointed that SSIS datasource is still unsupported and experimental in SQL Server 2008, as it was in 2005. This is a critical component in our report environment and I NEED it to work. Please help!

Having the same problem.In the event log I also find some remarks about the NT AUTHORITY\NETWORK SERVICE account. Not exactly clear to me if thatcauses the error.But it is worth a try.I tried to add the NT AUTHORITY\NETWORK SERVICE account to the DB, but in our environment there doesn't seem to be such an account. Got the following message: Windows NT user or group 'NT AUTHORITY\NETWORK SERVICE' not foundDo I have to create this account? (I'm a developer and have to go to other people to get this done).Isn't it possible for the SSIS package to access the DB with the account the RS or the IS services are using?

>sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE' and granted access to the DB the package accesses.This only works if your SSIS package is accessing a local data source server. The built-in network service account is local to the current machine. For remote access you may need to use a domain account or investigate other options.>What I don't understand is why the unattended execution account permissions were not used.This is currently a limitation in SSIS. Unfortunately it can't use the unattended execution account.HTH,RobertRobert Bruckner; http://blogs.msdn.com/robertbruckner; This posting is provided "AS IS" with no warranties, and confers no rights.

I have the same problem. Configured everything to the last detail. Using SSIS/SSRS 2008. SQL Server Service accounts have local admin rights (for testing). Keep getting: An error occurred during client rendering.An error has occurred during report processing.Query execution failed for dataset 'DataSet1'.The package failed to execute.The SSIS package is very simple. I have even tried using a flat file source. One thing I have noticed is that if I force the execution result of the SSIS package to "success", the last message changed from failed to execute to "object not set to an instance of an object" I've looked everywhere for a solution to this. Any ideas? Appreciated

I have the same problem. Configured everything to the last detail. Using SSIS/SSRS 2008. SQL Server Service accounts have local admin rights (for testing). Keep getting: An error occurred during client rendering. An error has occurred during report processing. Query execution failed for dataset 'DataSet1'. The package failed to execute. The SSIS package is very simple. I have even tried using a flat file source. One thing I have noticed is that if I force the execution result of the SSIS package to "success", the last message changed from failed to execute to "object not set to an instance of an object" I've looked everywhere for a solution to this. Any ideas? Appreciated I have the exact same issue! How did you solve this? Thanks

Hi, Using SSRS datasource with SSIS, please check the below tip and links associcated to get the complete idea on how to do that. http://beyondrelational.com/justlearned/posts/749/how-to-use-ssis-package-as-ssrs-data-source.aspxThanks Karthikeyan Anbarasan http://f5debug.net/

I have the same problem but in context of sharepoint 2007,I Configured everything to the last detail. Using SSIS/SSRS 2008. SQL Server Service accounts have local admin rights (for testing). Keep getting: An error occurred during client rendering. An error has occurred during report processing. Query execution failed for dataset 'DataSet1'. The package failed to execute. The SSIS package is very simple and work fine in BIDS solution but when i deploy this on sharepoint 2007 and try to run this report then it throws above errors,even i created this solution on sharepiont 2007 server,deployed this on my sharepoint 2007 application and checked the report by admin cridential,but same error raised again. I search allot and find nothing to help me out from this prob,so if anyone got its solution then plz reply me ASAP. -Thanks

I have the same problem but in context of sharepoint 2007,I Configured everything to the last detail. Using SSIS/SSRS 2008. SQL Server Service accounts have local admin rights (for testing). Keep getting: An error occurred during client rendering. An error has occurred during report processing. Query execution failed for dataset 'DataSet1'. The package failed to execute. The SSIS package is very simple and work fine in BIDS solution but when i deploy this on sharepoint 2007 and try to run this report then it throws above errors,even i created this solution on sharepiont 2007 server,deployed this on my sharepoint 2007 application and checked the report by admin cridential,but same error raised again. I search allot and find nothing to help me out from this prob,so if anyone got its solution then plz reply me ASAP. -Thanks