SSIS：创建具有不同行格式的平面文件 [英] SSIS : Creating a flat file with different row formats

问题描述

我想创建一个平面文件输出，其中行格式不同。
文件具有标题，中间数据行，页脚行。
文件如下所示

I want to create a flat file output, where format of rows is different. file has header, middle data rows, footer row. file will look Like below

H|deptcode123|deptNameXYZ|totalemp300   
E|Sam|Johnson|address1|empCode1|........many other columns
E|Sam2|Johnson2|address2|empCode2|........many other columns
E|Sam4|Johnson3|address3|empCode3|........many other columns
E|Sam5|Johnson4|address4|empCode4|........many other columns         
J|300|250000

如何在SSIS中生成此文件。输入将来自不同的表，我打算编写3个独立的查询/ sp以获取页眉，中间行和页脚行记录。

How can I generate this file in SSIS. Input will come from different tables, I am planning to write 3 separate queries/ sp's to get the header, middle row and footer row record.

推荐答案

为此，您需要每种不同类型的行集的数据流和连接管理器。例如，要具有不同的页眉，正文和页脚，您将需要3个数据流和3个平面文件连接管理器。每个平面文件连接管理器都指向同一文件。诀窍是要确保未选中平面文件目标中的覆盖文件中的数据设置。这样，每个数据流都将执行并追加到文件，并且每个数据流都可以具有其离散列和数据类型。

To do this you need a data flow and connection manager for each different type of rowset. For example to have different header, body, and footer you would need 3 dataflows and 3 flat file connection managers. Each flat file connection manager points to the same file. The trick is to make sure the setting Overwrite data in the file in the Flat File destination is unchecked. This way each data flow executes and appends to the file and each data flow can have its discrete columns and data types.

这篇关于SSIS：创建具有不同行格式的平面文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！