如何在SSIS的Foreach循环中动态逆透视（Unpivot）年份列数据？

这问题我之前帮好几个同行踩过坑，SSIS原生的Unpivot组件确实死磕静态列，遇到动态年份列直接歇菜。给你三个落地性拉满的方案，按需挑：

这是最灵活的解决方案，完全绕开原生Unpivot的静态列限制，自定义逻辑想怎么转就怎么转：

• 先在Control Flow的Foreach循环里，把当前遍历到的文件路径/文件名赋值给一个变量（比如@CurrentFileName）
• 转到Data Flow，把源文件的连接字符串设为动态（用变量替换固定路径）
• 添加脚本组件（选Transformation类型），将输入列设置为“全部输入列”（或者后续用代码动态读取，更灵活）
• 打开脚本编辑器，做这几件事：
  1. 在“ReadOnlyVariables”里添加刚才的@CurrentFileName变量，方便从文件名解析年月
  2. 定义输出列：Year（int类型）、Month（string类型）、SalesValue（decimal类型，根据你的数据调整）
  3. 用C#/VB写核心逻辑：遍历输入行的所有列，筛选出年份列（比如列名是纯数字的年份，像2020、2021），然后为每个符合条件的列生成一行新数据，把年份、月份、销售值对应填进去

给你一段C#的核心代码参考：

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // 从文件名解析年月，假设文件名格式是Sales_202301.csv
    string fileName = Variables.CurrentFileName;
    string yearPart = fileName.Substring(fileName.IndexOf('_') + 1, 4);
    string monthPart = fileName.Substring(fileName.IndexOf('_') + 5, 2);
    int year = int.Parse(yearPart);

    // 遍历所有输入列
    foreach (IDTSInputColumn100 col in ComponentMetaData.InputCollection[0].InputColumnCollection)
    {
        string colName = col.Name;
        int colYear;
        // 判断列名是否是有效年份（这里假设列名就是数字年份）
        if (int.TryParse(colName, out colYear) && colYear >= 2000 && colYear <= DateTime.Now.Year + 1)
        {
            // 获取该列的销售值，注意适配你的数据类型
            object value = Row.GetType().GetProperty(colName).GetValue(Row);
            if (value != DBNull.Value)
            {
                decimal salesValue = Convert.ToDecimal(value);
                // 输出新行
                Output0Buffer.AddRow();
                Output0Buffer.Year = colYear;
                Output0Buffer.Month = monthPart;
                Output0Buffer.SalesValue = salesValue;
            }
        }
    }
}

• 小提醒：记得处理空值，如果销售值为空就跳过，避免插入无效数据

如果你的源文件是CSV或Excel，也可以把文件先读到临时表，再用动态SQL做逆透视，适合喜欢用SQL的同学：

• 在Control Flow的Foreach循环里，把文件路径赋值给变量@FilePath
• 添加执行SQL任务，写动态SQL读取文件到临时表：

DECLARE @sql NVARCHAR(MAX), @fileDir NVARCHAR(500), @fileName NVARCHAR(200)

-- 拆分文件路径和文件名
SET @fileDir = LEFT(@FilePath, CHARINDEX('\', @FilePath, LEN(@FilePath) - CHARINDEX('\', REVERSE(@FilePath)) + 1))
SET @fileName = RIGHT(@FilePath, CHARINDEX('\', REVERSE(@FilePath)) - 1)

-- 动态读取文件到临时表
SET @sql = N'
SELECT * INTO #TempSales
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
''Text;Database=' + @fileDir + ';HDR=YES;FMT=Delimited'',
''SELECT * FROM [' + @fileName + ']'')
'
EXEC sp_executesql @sql

• 接着再用动态SQL提取年份列，做逆透视并插入目标表：

DECLARE @unpivotSql NVARCHAR(MAX), @yearCols NVARCHAR(MAX)

-- 获取临时表中所有年份类型的列名
SELECT @yearCols = STRING_AGG(QUOTENAME(name), ',')
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#TempSales')
AND ISNUMERIC(name) = 1 -- 筛选数字列（假设年份列都是数字）

-- 动态逆透视并插入目标表
SET @unpivotSql = N'
INSERT INTO YourTargetTable (Year, Month, SalesValue)
SELECT 
    CAST(YearCol AS INT) AS Year,
    SUBSTRING(''' + @fileName + ''', CHARINDEX(''_'', ''' + @fileName + ''') + 5, 2) AS Month,
    SalesValue
FROM #TempSales
UNPIVOT (
    SalesValue FOR YearCol IN (' + @yearCols + ')
) AS unpvt
WHERE SalesValue IS NOT NULL
'
EXEC sp_executesql @unpivotSql

• 注意：需要提前安装Microsoft ACE OLEDB驱动，并且确保CSV的分隔符、表头格式统一

如果你能控制源文件的生成逻辑，那直接改源文件的列结构是最省事的：把原来的“2020、2021”这类列，拆成每行对应“年份、销售值”的结构。比如原来一行是Product,2020,2021，改成两行Product,2020,100和Product,2021,200。这样之后，SSIS里直接用原生Unpivot组件（甚至不用Unpivot）就能轻松处理，完全不会有动态列的问题。当然，这个方案的前提是你能修改源文件的生成规则。

• 不管用哪个方案，一定要加错误处理：比如Foreach循环里加错误分支，捕获文件读取失败的情况
• 测试时先拿一两个文件验证逻辑，没问题再批量跑
• 如果文件名的年月格式不固定，建议从文件内容里提取年月（如果数据里有），比解析文件名更可靠

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