Thursday, August 19, 2010

Write data from Multiple tables to single flat file

It's simple if you are transferring your single table data to a single file means its one-to-one mapping. Many threads I observed at MSDN forum where user want different set of data (Header, Detail, Trailer) in a single file or data from the multiple tables to single flat file. It's not difficult in SSIS but it is tricky.

USE 3 dataflow tasks one for each table with precedence constraints to have them execute in the correct order.

Each destination would use its own connection manager but they would all point to the same flat file. The only thing you would need to do is in the flat file destination property grid (or advanced editor) make sure you set the overwrite flag to false for the second and third dataflows so that the file will be appended to. 

DFT 1:
OLEDB Src (header table) --> FlatFile Dest
(using flat file conn mgr 1 (filename = OutputFile.csv), overwrite set to true)

DFT 2:
OLEDB Src (details table) --> FlatFile Dest
(Using flat file conn mgr 2 (filename = OutputFile.csv), overwrite set to false)

DFT 3:
OLEDB Src (trailer table) --> FlatFile Dest
(Using flat file conn mgr 3 (filename = OutputFile.csv), overwrite set to false)

Get the sample package..
For step-by-step explanation, please check the following document.

There are lots of other ways to do this. but in the above method, you don't need any specific programming skill. Do let me know if you have any doubt..