如何生成带混合格式表头表尾的CSV？SSIS最优方案解析

针对你需要将3个TSQL CTE合并的人口统计数据生成特定格式CSV的需求——其中表头、表尾为制表符分隔，数据块为逗号分隔，且表头表尾信息来自存储提交ID的SQL表——之前考虑过SSRS自动化，最终确定SSIS是最优实现路径，具体步骤如下：

需求回顾 #

生成的CSV需符合以下格式规则：

• 表头行：制表符分隔，内容取自存储提交ID的SQL表
• 数据行：逗号分隔，为CTE合并后的人口统计明细
• 表尾行：制表符分隔，内容同样取自存储提交ID的SQL表

示例输出（模拟数据）：

0001011RNL	DBS	473520180116
10,6374635,19540714,,,7253647987,p1,,john,,2,3 MADE UP ST,,local,CUMBRIA,,ca12 0T1,,,,,,,,A82635,,
10,9283746,19650325,,,7536482965,p2,,peter,,2,38 MADE UP ST,,WIGTON,,,ca12 0T3,,,,,,,,A82045,,
9901011RNL	DBS	473520180116

具体SSIS实现步骤 #

1. 控制流规划：在SSIS的控制流选项卡中添加2个数据流任务，分别对应「表头+明细数据写入」和「表尾数据追加写入」两个环节。

2. 第一个数据流任务（表头+明细）：

   • 配置数据源：同时读取两个来源的数据——一是3个CTE合并后的人口统计明细，二是存储提交ID的SQL表中的表头信息。
   • 数据转换处理：使用派生列等转换组件，将明细数据处理为逗号分隔的格式；同时提取表头字段，组装成制表符分隔的单行表头内容。
   • 写入目标文件：配置Flat File Destination，通过SSIS变量动态传入表头内容，将表头和明细行写入目标文件（注意设置「Overwrite」属性为True，确保每次生成新文件）。

3. 第二个数据流任务（表尾追加）：

   • 生成表尾数据：使用脚本组件作为数据源，从存储提交ID的SQL表中读取表尾信息，组装成制表符分隔的单行表尾内容，并将其映射到SSIS变量。
   • 追加写入文件：配置同一个Flat File Destination，将「Overwrite」属性设置为False，这样就能将表尾行追加到已生成的文件末尾，完成整个CSV的生成。

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