Click here to Skip to main content
Click here to Skip to main content
Go to top

How to Use a Multi-Result Set Stored Procedure in SSIS

, 31 Dec 2008
Rate this:
Please Sign up or sign in to vote.
How to use a script component as a data source for multi-result set Stored Procedures.

Introduction

Recently, I had the pleasure of working with a data source that returned all of the transactions with one Stored Procedure. However, this meant the Stored Procedure returned 14 result sets and an output parameter. The question was how to get the 15 result sets into SSIS. The answer: use a script component. This article describes the path I took to handle this.

Making the connection

All connections in SSIS are handled through connection managers. We will have to have a connection manager to connect to the source database to execute the Stored Procedure.

In the Connection Manager tab of SSIS, right click and select New ADO.NET Connection…

Setup your connection properties, and click on Test Connection. Once you have a good connection, you are ready for the next step.

Setup a script component

I added a script component to my data flow task. When asked if it was a Source, Transformation, or a Destination, I selected Source.

I then added a total of 15 outputs (14 for the result sets, 1 for the output parameter). To do this, I clicked on the Inputs and Outputs tab, and clicked on the Add Output button until I had 15 outputs.

Then came the fun part: adding, naming, and typing all of the columns for all of the outputs. On the same Inputs and Outputs tab, I selected the first output, and renamed it to the result set name. Then, I opened up the output in the tree view and expanded the Output Columns folder. I clicked on the Add Column button until I had as many columns as the first result set.

Once the columns where in the tree view, I selected the first one, changed the name, set the data type and size, and moved onto the next column, until they were complete.

Then, I did the same for each output in the component.

The final step here is to configure the script component to use your newly created connection manager. To do this, click on the Connection tab and add a new connection. Set the name, and then in the middle column, choose your connection manager.

Scripting the outputs

The next step is to tie together the Stored Procedure and the script component outputs. To do this, click on the Script tab and click the Design Script button to open the scripting window. I added two subroutines to handle opening the connection and executing the Stored Procedure:

Public Class ScriptMain
Inherits UserComponent

Private connMgr As IDTSConnectionManager90
Private Conn As SqlConnection
Private Cmd As SqlCommand
Private sqlReader As SqlDataReader


Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

    'This is the connection to your connection manager.
    connMgr = Me.Connections.Connection
    Conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub

Public Overrides Sub PreExecute()

    Dim cmd As New SqlCommand("Declare @SessionID int; Exec " & _ 
            "spgTransactions @SessionID OUTPUT; Select @SessionID", Conn)
    sqlReader = cmd.ExecuteReader

End Sub

The AcquireConnections subroutine is called by SSIS when it is ready to open the database connections. I override it to make sure the database connection is ready to use.

Likewise, the PreExecute is called when it’s time to get the data. (This should clear up some of the long running PreExecute issues out there.) I open our SQL Reader here and execute the source Stored Procedure.

Now comes another fun part. Linking the result sets to the output columns.

This is done in the CreateNewOutputRows subroutine:

Public Overrides Sub CreateNewOutputRows()

'Invoice Header
Do While sqlReader.Read
    With Me.InvoiceHeaderBuffer
        .AddRow()
        .InvoiceNumber = sqlReader.GetInt32(0)
        .InvoiceDate = sqlReader.GetDate(1)
        'etc, etc, etc for all columns.
    End With
Loop

sqlReader.NextResult()

'Invoice Detail
Do While sqlReader.Read
    With Me.InvoiceDetailBuffer
        .AddRow()
        ...

        ' more outputs and more columns
        ' until we get to the last result set
        ' which will be the output parameter (SessionID)

        sqlReader.NextResult()

        'Session ID
        'We know this result set has only 1 row
        sqlReader.Read
        With Me.SessionIDBuffer
        .AddRow()
        .SessionID = sqlReader.GetInt32(0)
    End With

    sqlReader.Read 'Clear the read queue

End Sub

This code goes through each result set in the SQL Reader, and assigns the value of the result set to the output column. I did not show all of the columns or all of the output, since it’s the same concept for each.

Once that is done, I clean up after myself:

Public Overrides Sub PostExecute()
    sqlReader.Close()
End Sub

Public Overrides Sub ReleaseConnections()
    connMgr.ReleaseConnection(Conn)
End Sub

This closes the SQL Reader and releases the connection to the database. Once this is done, close the script window, and click on OK on the script component properties. The script component will now have multiple outputs that you can select from when linking it to another data flow component.

Conclusion

I hope this will help you when you need to return multiple result sets from Stored Procedures into SSIS. If you are familiar with VB.NET coding, you should pick this up easily, and even if not, the example at least gives you the basic steps and something to copy from.

Peace!

Read my blog here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

BobP1339
Database Developer
United States United States
I am a freelance Business Intelligence consultant, specializing in Microsoft technologies.
 
I pride myself in solving problems for the business. I understand that my role is to support business and help solve technology and information problems and remove roadblocks.
 
I have expertise in the telecom, mortgage and travel industries, but am not limited to any industry.
 
I have 16 years of professional experience, ranging from SQL Server DBA, C# Developer, Business Intelligence, and Software Development Management.
 
I am now accepting new clients. If you would like to retain my services, please feel free to contact me at bobp1339 at yahoo dot com.
 
I am also available to mentor new SSRS and SSIS users. I can show them the best practices and quickest ways to get the job done.

Comments and Discussions

 
QuestionNice post - have a question. PinmemberGomtiGomti15-Dec-13 18:31 
AnswerRe: Nice post - have a question. PinmemberBobP133915-Dec-13 19:09 
QuestionHow to Use a Multi-Result Set Stored Procedure in SSIS Pinmembervbwrangler25-Jun-10 8:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 31 Dec 2008
Article Copyright 2008 by BobP1339
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid