微软BI 之SSIS 系列 - 带有 Header 和 Trailer 的不规则的平面文件输出处理技巧

案例背景与需求介绍

之前做过一个美国的医疗保险的项目，保险提供商有大量的文件需要发送给比如像银行，医疗协会，第三方服务商等。比如像与银行交互的 ACH 文件，传送给协会的 ACH Credit 等文件。这些文件格式在美国都是开放的，通用的，可以直接到相关网站下载。也就是说像银行，协会等他们接受这种固定格式的文件，读取数据，读取公司编号进行业务来往或者记录。我当时就是直接在网上搜索到一个 PDF 格式的文件说明，大概有10来页，就是告诉你这个格式是如何定义，应该如何来处理的。

那么这种文件并非像我们通常看到的一个文件所有的行都是遵循一种格式，这种文件分为很多 Section：

1. File Header/Trailer
2. Batch Header/Trailer
3. 还有细节的交易数据等

可以参看 - http://www.treasurysoftware.com/ach.html 或 http://www.treasurysoftware.com/ach-file-format.html 

会发现第1,2,3-5,6,7 行它们的格式都不一样，不同于我们之前的平面文件输出，在同一种格式下批量输出。

它们的文件格式说明就明确规定了某一行的输出有多少列，每列应该是什么内容，宽度，格式等等都严格的定义了。

案例演示与讲解

下面我使用 Adventure Works DW 2012 数据中的数据模拟输出类似于这种格式的平面文件。

比如下方是我的一个文件示例，第一行是单独的一个 Section，第二行也是，自第三行起到倒数第二行的格式是相同的，最后一行又是独立的一个输出格式。那么这个文件就是由 4 个 Section 组成。

 

假设格式的定义如下所示：

一般来说，对于 Header 或者 Trailer 中数据通常以描述性数据为主。比如像 Header 1 中的标题部分 -  ADVENTUREWORKS EMPLOYEE INFORMATION  这些都是固定的长度。但同时也有动态变化的内容，比如 Header 1 中的文件输出日期，以及 Header 2 中的员工数量。像 Trailer 部分，一般也是这种描述性的数据为主。

那么如何来输出这种格式的数据，很显然我们不能直接指定一个数据源表，然后将表中的内容给查询出来然后控制好长度直接输出，因为几个不同 Section 部分的格式是不一样的。我们的做法是，将 Header 1 和 Header 2 这样占少量行的数据拼接成一个整体输出；而对于像 Content 部分细节数据则使用数据库表操作自然输出。

 

同时，还要解决一个问题，如何让这不同格式的 Section 能够输出到同一个文件？这些在下面的步骤中都将能看到。

下面这三部分的数据将为 OLE DB Source 提供数据查询结果 -

对于 Header 1 和 Header 2 的处理

创建存储过程，此存储过程输出一个两行单列的表数据。

IF OBJECT_ID('T006_GET_EMPLOYEE_FILE_HEADERS') IS NOT NULL
DROP PROCEDURE T006_GET_EMPLOYEE_FILE_HEADERS
GO

CREATE PROCEDURE T006_GET_EMPLOYEE_FILE_HEADERS
AS
BEGIN
 SET NOCOUNT ON

 DECLARE @EMPLOYEE INT
 SELECT @EMPLOYEE = COUNT(*)
 FROM T006_EMPLOYEE

 SELECT 'FILE CREATED DATE:' + -- Description
   CONVERT(VARCHAR(12),GETDATE(),110) + -- File Created Date
   SPACE(20) + -- 80 spaces
   '**********' +
   ' ADVENTUREWORKS EMPLOYEE INFORMATION ' + -- Company Report Name
   '**********' +
   SPACE(95) AS HEADER
 UNION
 SELECT 'TOTAL EMPLOYEES:' +
   CONVERT(VARCHAR(10),@EMPLOYEE) +
   SPACE(184-LEN(CONVERT(VARCHAR(10),@EMPLOYEE))) AS HEADER
END
GO

对于 Trailer 的数据处理

创建存储过程，输出一个一行一列的表数据。

IF OBJECT_ID('T006_GET_EMPLOYEE_FILE_TRAILERS') IS NOT NULL
DROP PROCEDURE T006_GET_EMPLOYEE_FILE_TRAILERS
GO
CREATE PROCEDURE T006_GET_EMPLOYEE_FILE_TRAILERS
AS
BEGIN
 SET NOCOUNT ON
 SELECT REPLACE(SPACE(92),' ','*') +
   ' ADVENTUREWORKS ' +
   REPLACE(SPACE(92),' ','*') AS TRAILER
END

对于 Content 的数据处理 

直接使用查询即可，然后在输出文件的时候确保各列输出的格式与长度。

SELECT FirstName +' '+LastName AS CustomerName,
 Title,
 HireDate,
 BirthDate,
 EmailAddress,
 Phone,
 MaritalStatus
FROM T006_EMPLOYEE

在控制流中，需要由3个数据流包构成，分别操作 Header 部分的输出，Content 部分的输出以及 Trailer 部分的输出。

连接管理器 CM_FF_EMPLOYEE_HEADER 接受来自于 T006_GET_EMPLOYEE_FILE_HEADERS 存储过程的输出。

连接管理器 CM_FF_EMPLOYEE_CONTENT 接受 SELECT 查询结果，同时要注意控制各列按指定宽度输出。

连接管理器 CM_FF_EMPLOYEE_TRAILER 接受存储过程 T006_GET_EMPLOYEE_FILE_TRAILERS 的输出。

但是要注意，它们指向的都是同一个目标平面文件。当然，马上就会有人问：那么不会出现后一个文件将前一个文件覆盖掉的后果吗？

这就是本案例处理的关键点所在 - 在 CM_FF_EMPLOYEE_HEADER 目标编辑器中选中 Overwrite data in the file - 覆盖已存在的文件。

但是在后面两个输出中将不会选择这个选项，那么后面两个输出 Content 和 Trailer 将会紧接着 Header 写入到同一个文件中形成一个整体。

 最终的输出效果如下 - 

处理平面文件还需要了解这几篇文章

微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节

微软BI 之SSIS 系列 - 平面文件格式的区别(Delimited,Fixed width,Ragged Right, Fixed width ...) 

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)  如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。