如何用SSIS与SQL Server将JSON扁平表转为规范化报表模型？

我来给你两种实用的方案，不管你想用T-SQL直接处理，还是用SSIS做可视化ETL流程，都能搞定这个字段数量不固定的转换需求。

T-SQL 动态Unpivot方案（推荐，适配字段动态变化） #

因为你的List_ABC_n和List_Type_n字段数量不固定，动态生成SQL是最灵活的方式——不用每次新增字段都手动修改代码。

实现步骤 #

核心思路是先从系统视图提取所有带序号的字段，再通过动态SQL把每组配对的字段转成一行，同时提取序号作为Sequence列。假设你的扁平表名为Flat_Orders，完整脚本如下：

DECLARE @sql NVARCHAR(MAX);
DECLARE @abcColumns NVARCHAR(MAX);
DECLARE @typeColumns NVARCHAR(MAX);
DECLARE @sequenceValues NVARCHAR(MAX);

-- 提取所有List_ABC_前缀的字段，按序号排序
SELECT @abcColumns = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('Flat_Orders')
AND name LIKE 'List_ABC[_]%'
ORDER BY CAST(SUBSTRING(name, CHARINDEX('_', name, CHARINDEX('_', name)+1)+1, LEN(name)) AS INT);

-- 提取所有List_Type_前缀的字段，按序号排序
SELECT @typeColumns = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('Flat_Orders')
AND name LIKE 'List_Type[_]%'
ORDER BY CAST(SUBSTRING(name, CHARINDEX('_', name, CHARINDEX('_', name)+1)+1, LEN(name)) AS INT);

-- 生成对应的Sequence值（0,1,2...）
SELECT @sequenceValues = STRING_AGG(CAST(CAST(SUBSTRING(name, CHARINDEX('_', name, CHARINDEX('_', name)+1)+1, LEN(name)) AS INT) AS NVARCHAR), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('Flat_Orders')
AND name LIKE 'List_ABC[_]%'
ORDER BY CAST(SUBSTRING(name, CHARINDEX('_', name, CHARINDEX('_', name)+1)+1, LEN(name)) AS INT);

-- 动态生成转换SQL
SET @sql = N'
SELECT 
    o.Order_Id,
    s.Sequence,
    o.Customer,
    o.BillingNumber,
    abc.ABC,
    type.Type
FROM Flat_Orders o
CROSS APPLY (
    VALUES 
        (' + REPLACE(@abcColumns, ', ', '), (') + ')
) abc(ABC)
CROSS APPLY (
    VALUES 
        (' + REPLACE(@typeColumns, ', ', '), (') + ')
) type(Type)
CROSS APPLY (
    VALUES 
        (' + REPLACE(@sequenceValues, ', ', '), (') + ')
) s(Sequence)
WHERE abc.ABC IS NOT NULL OR type.Type IS NOT NULL -- 过滤完全为空的无效行
ORDER BY o.Order_Id, s.Sequence;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

额外说明 #

• 如果需要把结果永久保存为表，只需在SELECT前添加INTO Normalized_Orders即可；
• 脚本会自动适配新增的List_ABC_n/List_Type_n字段，无需修改代码；
• 自动过滤掉ABC和Type都为空的行（比如Order_1002的Sequence 1、2）。

SSIS方案（适合可视化ETL流程） #

如果你更习惯用SSIS做数据转换，也可以通过Unpivot组件+脚本任务实现，不过因为字段数量不固定，需要动态配置列映射：

实现步骤 #

1. 配置数据源：连接到SQL Server数据库，选择扁平表Flat_Orders作为数据源；
2. 动态获取字段名：在数据流任务前添加脚本任务，用C#/VB.NET查询系统视图，提取所有List_ABC_n和List_Type_n字段名，存入SSIS变量（比如@ABCColumns、@TypeColumns）；
3. Unpivot处理ABC字段：将数据源连接到Unpivot组件，动态设置待转换列为@ABCColumns中的字段，目标列设为ABC，并从字段名中提取序号生成Sequence_ABC列；
4. Unpivot处理Type字段：重复上述步骤，处理List_Type_n字段，生成Type和Sequence_Type列；
5. 合并结果集：用Merge Join组件，按Order_Id和Sequence_ABC=Sequence_Type连接两个Unpivot的结果，保留Customer、BillingNumber字段；
6. 过滤空值：用Conditional Split组件过滤掉ABC和Type都为空的行；
7. 加载到目标表：将最终结果写入规范化的目标表。

额外说明 #

SSIS方案适合需要可视化监控ETL流程的场景，但动态列配置需要修改包元数据，复杂度比T-SQL高；如果字段经常变动，T-SQL的动态方案会更省心。

两种方案都支持定时调度：T-SQL可封装为存储过程，用SQL Agent定时执行；SSIS包可通过SQL Agent或SSIS Catalog调度运行。

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