如何用SSIS的ForEach Loop Container逐行写入平面文件

核心思路 #

先提取所有学生的唯一ID，通过ForEach Loop遍历每个学生ID，针对单个学生拉取完整数据后，转换为要求的行格式，最终生成专属的平面文件。

具体步骤 #

1. 获取学生唯一ID集合

   • 添加Execute SQL Task，执行查询获取不重复的学生ID：

     SELECT DISTINCT StudentID FROM StudentScores
     

   • 将结果集设置为Full result set，映射到一个Object类型的变量（例如@StudentIDs）。

2. 配置ForEach Loop容器

   • 添加ForEach Loop Container，选择Foreach ADO Enumerator，数据源指定为@StudentIDs变量。
   • 在变量映射中，把枚举得到的学生ID绑定到字符串变量（例如@CurrentStudentID）。

3. 循环内提取单个学生数据

   • 在循环内部添加Execute SQL Task，查询当前学生的完整数据，同时计算总科目数：

     SELECT 
         StudentID, 
         Gender, 
         Subject, 
         Score, 
         COUNT(*) OVER() AS TotalSubjects 
     FROM StudentScores 
     WHERE StudentID = ?
     

   • 参数映射中绑定@CurrentStudentID，结果集设为Full result set，映射到Object类型变量@StudentData。

4. 转换为目标行格式

   • 添加Data Flow Task到循环内，数据流中：
     • 用ADO.NET Source读取@StudentData的内容。
     • 添加Script Component（转换类型），在脚本中处理格式：
       • 提取并保留学生的ID、性别、总科目数。
       • 将每个科目+成绩组合成单独一行。
       • 输出仅保留一个字符串列，存储每行的最终内容（确保无空列）。

5. 动态生成平面文件

   • 添加Flat File Destination，文件路径用变量动态生成（例如@FilePath，表达式设为"C:\\StudentFiles\\Student_" + @CurrentStudentID + ".txt"）。
   • 配置平面文件格式，确保每行仅输出内容列，无多余空列。

核心思路 #

直接通过ScriptTask连接数据库读取全量数据，按学生ID分组后，逐一生成符合格式的平面文件，无需依赖多个SSIS组件，灵活性更高。

具体步骤 #

1. 配置变量

   • 创建字符串变量@ConnectionString，存储数据库连接字符串。
   • 创建字符串变量@OutputFolder，指定输出文件的根目录（例如C:\\StudentFiles\\）。

2. 编写C#脚本示例

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.IO;
   using System.Collections.Generic;
   using System.Linq;
   using Microsoft.SqlServer.Dts.Runtime;
   
   public void Main()
   {
       // 读取变量配置
       string connStr = Dts.Variables["ConnectionString"].Value.ToString();
       string outputFolder = Dts.Variables["OutputFolder"].Value.ToString();
   
       // 创建输出目录（不存在则自动创建）
       if (!Directory.Exists(outputFolder))
       {
           Directory.CreateDirectory(outputFolder);
       }
   
       // 读取所有学生成绩数据
       List<StudentRecord> studentRecords = new List<StudentRecord>();
       using (SqlConnection conn = new SqlConnection(connStr))
       {
           conn.Open();
           string query = "SELECT StudentID, Gender, Subject, Score FROM StudentScores";
           using (SqlCommand cmd = new SqlCommand(query, conn))
           {
               using (SqlDataReader reader = cmd.ExecuteReader())
               {
                   while (reader.Read())
                   {
                       studentRecords.Add(new StudentRecord
                       {
                           StudentID = reader["StudentID"].ToString(),
                           Gender = reader["Gender"].ToString(),
                           Subject = reader["Subject"].ToString(),
                           Score = reader["Score"].ToString()
                       });
                   }
               }
           }
       }
   
       // 按学生ID分组处理
       var groupedStudents = studentRecords.GroupBy(r => r.StudentID);
       foreach (var studentGroup in groupedStudents)
       {
           string studentId = studentGroup.Key;
           var records = studentGroup.ToList();
           if (records.Count == 0) continue;
   
           // 生成文件路径
           string filePath = Path.Combine(outputFolder, $"Student_{studentId}.txt");
           using (StreamWriter writer = new StreamWriter(filePath))
           {
               // 写入ID行
               writer.WriteLine(records[0].StudentID);
               // 写入性别行
               writer.WriteLine(records[0].Gender);
               // 写入科目+成绩行
               foreach (var record in records)
               {
                   writer.WriteLine($"{record.Subject} {record.Score}"); // 可按需调整分隔符
               }
               // 写入总科目数行
               writer.WriteLine(records.Count.ToString());
           }
       }
   
       Dts.TaskResult = (int)ScriptResults.Success;
   }
   
   // 定义数据实体类
   private class StudentRecord
   {
       public string StudentID { get; set; }
       public string Gender { get; set; }
       public string Subject { get; set; }
       public string Score { get; set; }
   }
   
   enum ScriptResults
   {
       Success = 0,
       Failure = 1
   }
   

   • 注意：可根据实际需求调整科目与成绩的分隔方式；若数据库字段为非字符串类型，需添加对应的类型转换逻辑。

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