Post reply

SSIS package as datasource for SSRS report

  • October 24, 2008 at 10:21 am

    #200354

    I created an SSIS package that delivers an ADO.NET recordset as its output, and it works fine stand-alone. I then created an SSRS report that connects to the SSIS package as its data source (only data source). In the Data tab of the Report Designer, I get all of the expected data just fine, and even when I preview the report in the Designer, everything is fine.

    However, when I deploy the SSRS report to the ReportServer, I get the following error when I try to run the report:

    An error has occurred during report processing.

    Query execution failed for data set 'DataSet_MergedJobsAndTasks'.

    The package failed to execute.

    Any suggestions will be appreciated.

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA
    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • October 24, 2008 at 10:34 am

    #889350

    Please watch the forums in which you post. Moved to SSRS.

    No idea why this doesn't run. Are the SSRS and SSIS on the same server?

  • October 24, 2008 at 11:05 am

    #889365

    Sorry about the mistaken post place--I noticed that after the fact, and couldn't move it. Thanks for moving it for me.

    Yes, both the SSIS package and SSRS report are on the same server, using the same account (domain admin privileges).

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA
    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • October 29, 2008 at 9:59 am

    #891319

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

    Christophe

  • October 29, 2008 at 12:16 pm

    #891411

    Couple thoughts...

    Christophe - SSRS from it's inception has been able to use any ADO.NET data as a datasource. I've even used an Excel spreadsheet as a datasource.

    Regarding Hacker's issue, I'm not familiar with using SSIS as a datasource but when you get a similar error using a stored proc it indicates an 'execute' permissions problem with the specific proc. I'd check to make sure there are execute rights not just on the server and database but all the way down to the specific SSIS package as well.

    HTH

    [font="Comic Sans MS"]toolman[/font]
    [font="Arial Narrow"]Numbers 6:24-26[/font]

  • October 29, 2008 at 1:17 pm

    #891438

    I've learned something. Thanks !

    Christophe

  • July 2, 2009 at 3:30 pm

    #1019503

    Hi, Did you find out how fix this, i have the same problem

    thanks

  • February 23, 2011 at 6:58 am

    #1290367

    Hello,

    I wanted same solution what you developed. it possible to share the code?

    Thanks Advance,

    Elango

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

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