SSIS将多列查询结果转为单JSON列存入目标表的高效方案咨询

针对你遇到的5万行数据转JSON写入的性能问题，以下几个方案可以显著提升效率：

方案1：数据源查询阶段直接生成JSON（推荐） #

利用SQL Server内置的FOR JSON语法，在关联两张表的查询中直接将每行数据转换为JSON格式，这样Merge Join后的结果集直接包含目标JSON列，无需在SSIS中做额外转换。数据库引擎的批量处理能力远优于SSIS的脚本组件，能大幅减少处理时间。

示例查询（适配你的Merge Join逻辑，需保证排序符合Merge Join要求）：

SELECT
    -- 生成单条JSON对象，WITHOUT_ARRAY_WRAPPER避免生成数组
    (SELECT t1.*, t2.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS JsonData
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.JoinKey = t2.JoinKey
-- Merge Join要求输入已排序，这里按关联键排序
ORDER BY t1.JoinKey

将这个查询作为SSIS的数据源，后续直接将JsonData列写入目标表即可。此方案省去了SSIS内部的数据转换步骤，性能最优。

方案2：优化Script Component为批量处理 #

如果无法修改源查询，可将原逐行处理的Script Component改为批量缓存处理，减少逐行序列化的开销。通过缓存一定数量的行（如1000行）后批量序列化，降低SSIS组件的交互损耗。

示例代码（C#脚本，需引用Newtonsoft.Json）：

using System.Data;
using Newtonsoft.Json;

public class ScriptMain : UserComponent
{
    private DataTable _batchTable;
    private const int BatchSize = 1000;

    public override void PreExecute()
    {
        base.PreExecute();
        _batchTable = new DataTable();
        // 动态创建与输入列匹配的DataTable结构
        foreach (var inputCol in ComponentMetaData.InputCollection[0].InputColumnCollection)
        {
            _batchTable.Columns.Add(inputCol.Name);
        }
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        var dataRow = _batchTable.NewRow();
        // 复制当前行数据到DataTable
        foreach (DataColumn col in _batchTable.Columns)
        {
            dataRow[col.ColumnName] = Row.GetType().GetProperty(col.ColumnName).GetValue(Row);
        }
        _batchTable.Rows.Add(dataRow);

        // 达到批量大小则处理
        if (_batchTable.Rows.Count >= BatchSize)
        {
            ProcessBatch();
            _batchTable.Clear();
        }
    }

    public override void PostExecute()
    {
        base.PostExecute();
        // 处理剩余未批量的数据
        if (_batchTable.Rows.Count > 0)
        {
            ProcessBatch();
        }
    }

    private void ProcessBatch()
    {
        foreach (DataRow row in _batchTable.Rows)
        {
            var jsonStr = JsonConvert.SerializeObject(row);
            Output0Buffer.AddRow();
            Output0Buffer.JsonColumn = jsonStr;
        }
    }
}

注意：需在SSIS脚本项目中添加Newtonsoft.Json NuGet包，或手动引入DLL。

方案3：使用SQLCLR自定义函数 #

编写SQLCLR函数实现多列转JSON，然后在SSIS的Derived Column组件中调用该函数。SQLCLR函数的性能优于Script Component，因为它运行在数据库进程内，减少了跨进程通信的开销。

示例SQLCLR函数（C#）：

using Microsoft.SqlServer.Server;
using Newtonsoft.Json;
using System.Data.SqlTypes;

public class JsonConverter
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlString ConvertToJson(SqlString col1, SqlString col2, /* 其他列参数 */)
    {
        var data = new {
            Col1 = col1.Value,
            Col2 = col2.Value,
            // 映射所有需要转换的列
        };
        return new SqlString(JsonConvert.SerializeObject(data));
    }
}

将该函数部署到SQL Server后，在SSIS的Derived Column中使用表达式dbo.ConvertToJson(Col1, Col2, ...)生成JSON列。

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