Post reply

Multiple result sets returned from SP

  • December 4, 2007 at 7:20 am

    #121308

    I have a stored proc (provided by a 3rd party) that returns multiple result sets.

    Is it possible to use this as a data source within SSIS???

  • December 4, 2007 at 7:41 am

    #756942

    Yes, use an ADO.Net Connection Manager.

    Create an object variable.

    Put your sp in an execute sql task using the above connection.

    Then you can use a Foreach Loop Container to iterate through the 3 tables. Pick the Foreach ADO enumertor and set the enumeration mode to all tables.

    That should get you started.

    Process the data after that depends on the results.

    Do all three tables have the same columns?

    Norman

    DTS Package Search

    http://www.dtspackagesearch.com/

  • December 4, 2007 at 9:13 am

    #757017

    Thanks Norman. Glad to know it's possible.

    The SP actualy returns 14 result sets. The procedure has been written by a third party which mean I have no control over its design.

    What I need to achieve is a flattening of the data returned by the 14 sets - which are all, in some way, relational.

    If I can loop around the object variable, grabing each set and inserting the data into tables then that is a great help.

    Many thanks

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

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