如何使用具有多结果集的存储过程

引言

最近，我很荣幸能处理一个数据源，它使用一个存储过程返回所有事务。但是，这意味着存储过程返回 14 个结果集和一个输出参数。问题是如何将 15 个结果集导入 SSIS。答案：使用脚本组件。本文描述了我处理此问题的过程。

建立连接

SSIS 中的所有连接都通过连接管理器处理。我们需要一个连接管理器来连接到源数据库以执行存储过程。

在 SSIS 的“连接管理器”选项卡中，右键单击并选择“新建 ADO.NET 连接…”

设置您的连接属性，然后单击“测试连接”。一旦您有了良好的连接，您就可以进行下一步了。

设置脚本组件

我将一个脚本组件添加到了我的数据流任务中。当被问及它是源、转换还是目标时，我选择了源。

然后，我总共添加了 15 个输出（14 个用于结果集，1 个用于输出参数）。为此，我单击“输入和输出”选项卡，然后单击“添加输出”按钮，直到我有 15 个输出。

然后是有趣的部分：为所有输出添加、命名和键入所有列。在相同的“输入和输出”选项卡上，我选择了第一个输出，并将其重命名为结果集名称。然后，我在树视图中打开输出并展开“输出列”文件夹。我单击“添加列”按钮，直到我拥有与第一个结果集一样多的列。

列在树视图中后，我选择了第一列，更改了名称，设置了数据类型和大小，然后移到下一列，直到它们完成。

然后，我对组件中的每个输出都做了同样的事情。

这里的最后一步是将脚本组件配置为使用您新创建的连接管理器。为此，单击“连接”选项卡并添加新连接。设置名称，然后在中间列中，选择您的连接管理器。

编写输出脚本

下一步是将存储过程和脚本组件输出联系起来。为此，单击“脚本”选项卡并单击“设计脚本”按钮以打开脚本窗口。我添加了两个子例程来处理打开连接和执行存储过程

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

AcquireConnections 子例程由 SSIS 在准备好打开数据库连接时调用。我覆盖它以确保数据库连接已准备好使用。

同样，PreExecute 会在需要获取数据时调用。（这应该可以解决一些长期运行的 PreExecute 问题。）我在这里打开我们的 SQL Reader 并执行源存储过程。

现在是另一个有趣的部分。将结果集链接到输出列。

这在 CreateNewOutputRows 子例程中完成

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

此代码遍历 SQL Reader 中的每个结果集，并将结果集的值分配给输出列。我没有显示所有列或所有输出，因为每个列或输出的概念都相同。

完成后，我会清理自己

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

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

这将关闭 SQL Reader 并释放与数据库的连接。完成后，关闭脚本窗口，然后单击脚本组件属性中的“确定”。现在，脚本组件将具有多个输出，您可以在将其链接到另一个数据流组件时从中选择。

结论

我希望当您需要从存储过程将多个结果集返回到 SSIS 时，这将对您有所帮助。如果您熟悉 VB.NET 编码，您应该很容易掌握它，即使不熟悉，该示例至少也为您提供了基本步骤和一些可复制的内容。

祝好！

在此处阅读我的博客.