SSIS包中动态读取MySQL存储过程元数据的方案问询

针对多站点调用存储过程返回动态列导致SSIS任务失败的问题，以下是两种可行的动态处理方案：

方案一：脚本任务处理System.Object变量数据 #

你已经尝试用System.Object存储结果集，后续可以通过脚本任务直接解析并写入目标表：

1. 配置执行SQL任务：将存储过程的执行结果集设置为「完整结果集」，映射到System.Object类型变量（如objSPResult）。
2. 添加C#脚本任务：
   • 在脚本中引用System.Data、MySql.Data.MySqlClient（MySQL驱动）。
   • 将变量转换为DataTable：

     DataTable dt = (DataTable)Dts.Variables["User::objSPResult"].Value;
     

   • 拆分固定列与动态列：
     • 遍历DataTable的每一行，提取前16列的值，直接插入到固定目标表。
     • 对第17列及以后的列，循环每一列，将列名作为属性名、列值作为属性值，插入到Unpivot目标表（需关联固定表的唯一标识，比如用固定列中的主键）。
   • 示例插入逻辑（简化版）：

     using (MySqlConnection conn = new MySqlConnection(Dts.Variables["User::TargetConnStr"].Value.ToString()))
     {
         conn.Open();
         foreach (DataRow row in dt.Rows)
         {
             // 插入固定列
             string insertFixedSql = "INSERT INTO FixedTable (Col1, Col2, ..., Col16) VALUES (@p1, @p2, ..., @p16)";
             MySqlCommand cmdFixed = new MySqlCommand(insertFixedSql, conn);
             // 逐个添加参数并执行命令
     
             // 插入动态列（Unpivot）
             for (int i = 16; i < dt.Columns.Count; i++)
             {
                 string colName = dt.Columns[i].ColumnName;
                 object colValue = row[i] == DBNull.Value ? null : row[i];
                 string insertUnpivotSql = "INSERT INTO UnpivotTable (FixedID, AttrName, AttrValue) VALUES (@fixedId, @attrName, @attrValue)";
                 MySqlCommand cmdUnpivot = new MySqlCommand(insertUnpivotSql, conn);
                 cmdUnpivot.Parameters.AddWithValue("@fixedId", row["Col1"]);
                 cmdUnpivot.Parameters.AddWithValue("@attrName", colName);
                 cmdUnpivot.Parameters.AddWithValue("@attrValue", colValue);
                 cmdUnpivot.ExecuteNonQuery();
             }
         }
         conn.Close();
     }
     

方案二：临时表中转+动态SQL生成Unpivot逻辑 #

利用临时表存储存储过程结果，再通过动态SQL处理动态列：

1. 创建临时表并插入结果：
   在Foreach循环的每次迭代中，先创建临时表并插入存储过程结果：

   -- 先创建临时表（需匹配存储过程返回的固定列结构，动态列自动适配）
   CREATE TEMPORARY TABLE TempSPResult (
       Col1 INT, Col2 VARCHAR(50), ..., Col16 DATETIME,
       -- 动态列无需提前定义，MySQL会自动添加
   );
   -- 插入存储过程结果
   INSERT INTO TempSPResult CALL spITProd('2022-09-25 20:04:22.847000000', '2022-10-25 20:04:22.847000000');
   

2. 获取动态列名：
   查询系统表筛选出固定列之外的列：

   SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_NAME = 'TempSPResult' 
     AND COLUMN_NAME NOT IN ('Col1', 'Col2', ..., 'Col16');
   

3. 动态生成Unpivot SQL：
   拼接动态列名生成Unpivot语句并执行：

   SET @dynamicCols = (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TempSPResult' AND COLUMN_NAME NOT IN ('Col1', ..., 'Col16'));
   SET @unpivotSql = CONCAT(
     'INSERT INTO UnpivotTable (FixedID, AttrName, AttrValue) ',
     'SELECT Col1 AS FixedID, AttrName, AttrValue ',
     'FROM TempSPResult ',
     'UNPIVOT (AttrValue FOR AttrName IN (', @dynamicCols, ')) AS unpvt'
   );
   PREPARE stmt FROM @unpivotSql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   

   同时执行固定列插入：

   INSERT INTO FixedTable (Col1, ..., Col16) SELECT Col1, ..., Col16 FROM TempSPResult;
   

关键注意事项 #

• 方案一中需处理空值和数据类型转换，Unpivot表的AttrValue建议用VARCHAR兼容所有动态列类型。
• 方案二中MySQL临时表属于会话级，每次Foreach循环迭代会自动销毁，无需手动删除；若需跨任务访问，可改用全局临时表（CREATE GLOBAL TEMPORARY TABLE）。
• 优先用固定列名而非列位置判断，避免存储过程调整列顺序导致错误。

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