Pages

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.



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


Important: 
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.
OR
----------------------------------------------------------------------------------------------------


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..

1 comment:

  1. How to loop through multiple tables to build one flat file. Tables are of different lengths. I need to add a claim record (t1), then its status record (t2), then its detail record (t3) into one flat file. I also need to add headers and footers, but I know how to do that. The flat file would look like:
    HEADER Record - col1...
    CLAIM Record - col1, col2, col3, col4
    STATUS Record - col1, col2, col3
    DETAIL Record - col1, col2
    STATUS Record - col1, col2, col3
    DETAIL Record - col1, col2
    CLAIM Record - col1, col2, col3, col4
    Etc.
    .
    .
    FOOTER Record - col1...

    Thank you

    ReplyDelete