SSIS : Creating a flat file with different row formats

4
votes

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.

2
sql-serverssisetlssis-2012bids
Is the header just a the start, or do you have multiple groups of headers, detail, footers appearing? If you have multiple groups of H/D/F then you need to basically build it in a SQL statement. The two solutions below don't cater for headers reappearing in the middle – Nick.McDermaid
Header(H) as well as footer(F) will appear just once in a file, middle rows(D) will be multiple rows. – coder

2 Answers

4
votes

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.

enter image description here enter image description here

2
votes

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