SSIS : Creating a flat file with different row for

问题:

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

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.

回答1:

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.

回答2:

If you want to create a flat file where rows has with different metadata. You have to use a one column flat file connection manager. With Dt_WStr data type and length = 4000

Use 3 consecutive DataFlow task using the same Flat file destination

First one write the header, second one the middle rows, third one the footer.

You can concatenate values from the select statment or using a Script Component