如何将存储过程返回的表作为完整结果集传入SSIS脚本任务？

我来一步步帮你搞定这个需求，其实分两大步就能完成：配置执行SQL任务获取结果集，再在脚本任务里读取这个结果集。

第一步：配置Execute SQL Task（执行SQL任务） #

1. 打开你的Execute SQL Task编辑器，在General选项卡：
   • 选择对应的数据库连接管理器
   • SQL语句里输入：EXEC SP_CM_ValidateInput ?（问号是参数占位符，对应存储过程的@FileName参数）
   • 结果集类型选择Full result set（因为我们要获取完整的返回表）
2. 切换到Parameter Mapping选项卡：
   • 点击Add，选择你用来存储文件名的变量（比如User::FileName）
   • 方向选Input，数据类型选VARCHAR，参数名称填0（SSIS里参数索引从0开始）
3. 切换到Result Set选项卡：
   • 点击Add，结果名称填0，变量选择一个Object类型的变量（比如User::ValidationResultObject）——这个变量就是用来存储存储过程返回的整张表的

第二步：配置Script Task（脚本任务） #

1. 把刚才的User::ValidationResultObject变量添加到脚本任务的ReadOnlyVariables里（如果不需要修改变量，只读就够了）
2. 点击Edit Script进入脚本编辑器，根据你用的C#或VB编写处理逻辑：

C#示例代码 #

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.OleDb;

namespace ST_xxxxxxxxx
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            DataTable validationTable = new DataTable();
            
            // 将Object变量转换为DataTable
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.Fill(validationTable, Dts.Variables["User::ValidationResultObject"].Value);

            // 遍历结果集，按需处理数据
            foreach (DataRow row in validationTable.Rows)
            {
                string validationDesc = row["ValidationDescription"].ToString();
                int errorCount = Convert.ToInt32(row["ErrCnt"]);
                
                // 示例：输出到SSIS日志
                Dts.Events.FireInformation(0, "Validation Result", 
                    $"描述: {validationDesc} | 错误数量: {errorCount}", 
                    string.Empty, 0, ref fireAgain);
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

VB示例代码 #

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute>
<System.CLSCompliantAttribute(False)>
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()
        Dim fireAgain As Boolean = False
        Dim validationTable As New DataTable()

        ' 将Object变量转换为DataTable
        Dim adapter As New OleDbDataAdapter()
        adapter.Fill(validationTable, Dts.Variables("User::ValidationResultObject").Value)

        ' 遍历结果集处理数据
        For Each row As DataRow In validationTable.Rows
            Dim validationDesc As String = row("ValidationDescription").ToString()
            Dim errorCount As Integer = Convert.ToInt32(row("ErrCnt"))

            ' 输出到SSIS日志
            Dts.Events.FireInformation(0, "Validation Result", 
                $"描述: {validationDesc} | 错误数量: {errorCount}", 
                String.Empty, 0, fireAgain)
        Next

        Dts.TaskResult = ScriptResults.Success
    End Sub

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

End Class

关键注意事项 #

• 确保你的存储过程确实返回了结果集：你的SP_CM_ValidateInput最后有SELECT * FROM @ValidationResultTbl，这部分是没问题的
• 用来存储结果集的变量必须是Object类型，不能用其他类型
• 参数映射时要保证数据类型匹配：@FileName是VARCHAR(250)，所以对应的SSIS变量要是字符串类型，参数数据类型选VARCHAR

内容的提问来源于stack exchange，提问作者Dina Kleper