如何用SSIS加载列数动态变化的Excel文件并实现逆透视？

完全可以实现自动化处理动态表头的Excel文件，无需每次手动修改包配置，以下是具体操作步骤，全程可视化为主，仅需少量简单代码：

步骤1：配置Excel连接管理器 #

• 新建SSIS包，添加Excel连接管理器，选中目标Excel文件，勾选「第一行包含列名」。
• 不要用默认的「表或视图」模式，切换为SQL命令模式，执行查询：

  SELECT * FROM [Sheet1$]
  

  让连接管理器自动识别当前文件的列结构，为后续动态处理做准备。

步骤2：用Foreach循环收集动态列名 #

• 添加Foreach循环容器，选择「Foreach ADO枚举器」：
  1. 创建两个变量：@ColumnName（字符串类型，存储单列列名）、@UnpivotColumns（字符串类型，存储所有需要逆透视的列名，格式为[列名],[列名],...）。
  2. 在循环的「集合」选项卡，设置「ADO对象源变量」为新建的ADO对象变量（比如@ExcelColumns），用来读取Excel的列架构信息。
  3. 在「变量映射」选项卡，将@ColumnName映射到索引0。
• 循环内添加脚本任务，将非EmpID的列名收集到@UnpivotColumns中，代码直接复制即可：

  string colName = Dts.Variables["ColumnName"].Value.ToString();
  if (colName != "EmpID")
  {
      string currentCols = Dts.Variables["UnpivotColumns"].Value.ToString();
      if (!string.IsNullOrEmpty(currentCols)) currentCols += ",";
      Dts.Variables["UnpivotColumns"].Value = currentCols + "[" + colName + "]";
  }
  Dts.TaskResult = (int)ScriptResults.Success;
  

步骤3：配置动态逆透视转换 #

• 添加数据流动任务，里面添加Excel源，同样用SQL命令模式执行SELECT * FROM [Sheet1$]。
• 添加逆透视转换并连接到Excel源，右键选择「编辑」进入高级编辑器：
  1. 切换到「表达式」选项卡，找到Unpivot Columns属性，设置表达式为：

     @[User::UnpivotColumns]
     

  2. 设置Pivot Key Value Column Name为Date，Unpivoted Value Column Name为Value。
  3. 在「输入列」面板，将EmpID标记为「透视键」，其余列会自动被纳入逆透视流程。

步骤4：加载到目标数据源 #

• 逆透视转换的输出已经是你需要的格式：EmpID、Date、Value，直接连接到OLE DB目标或其他存储组件，完成列映射即可。

额外注意 #

• 确保Excel的日期列格式统一（如MM.yyyy），若有格式差异，可添加派生列转换统一日期格式。
• 若涉及多工作表，可扩展Foreach循环遍历工作表名，用变量动态设置Excel源的工作表名称。

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