4

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
coder
  • 122
  • 1
  • 13
  • 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.Mc Apr 08 '17 at 05:01
  • 1
    Header(H) as well as footer(F) will appear just once in a file, middle rows(D) will be multiple rows. – coder Apr 10 '17 at 14:11

2 Answers2

4

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

Chris Albert
  • 2,462
  • 8
  • 27
  • 31
  • 1
    I tried this, but the number of columns in each type is different. Header(H) has suppose 5 column as middle rows(D) has 20 columns, then max of this(20) is considered as number of columns in the flat file. And header row looks like this H|deptcode123|deptNameXYZ|totalemp300|||||||||... – coder Apr 10 '17 at 14:13
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

Hadi
  • 36,233
  • 13
  • 65
  • 124