Post reply

To Hold Multiple Result Sets in SSIS

  • February 18, 2011 at 5:22 am

    #233803

    Hi,

    I have a procedure which results a Header Record, a detail reocrd and a Trailer record, there are three result sets. We are not supposed to change the stored procedure. Is it possible to hold the result set through any task and write the result sets to a Flat file. Can you please help us on this.

    Thanks

    Dhana

  • February 18, 2011 at 7:34 am

    #1288848

    dhanasekar.palani (2/18/2011)

    Hi,

    I have a procedure which results a Header Record, a detail reocrd and a Trailer record, there are three result sets. We are not supposed to change the stored procedure. Is it possible to hold the result set through any task and write the result sets to a Flat file. Can you please help us on this.

    Thanks

    Dhana

    I'm not sure, but you can try executing the stored procedure in an Execute SQL Task and store the results as a full result set in an object variable.

    With the For Each Loop you should be able to loop over all the different resultsets.

    Another option is to use a script task. There you can use an OLEDBDataReader with an OLEDBCommand who can work with multiple result sets.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

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