Writing data from multiple tables to a single flat file

I have a package that contains three database tables (Header, detail and trailer record) each table is connected via a OLE DB source in SSIS. Each table varies in the amount of colums it holds and niether of the tables have the same field names. I need to transfer all data, from each table, in order, to a flat file destination.

I have created a flat file connector containing all the necessary fields that need to be transferred from the above three records.

Header record contains 1 row of data. 4 colums
Detail record may contain numerous rows. 28 columns.
Trailer record contains 1 row of data. 4 columns.

I need to write the row of data from the Header Rec. to the Flat file destination first, then I need to write the multiple rows of data from the Header Rec to the SAME file and lastly the rows from the Trailer Rec. need to be writtten to the SAME Flat file as well.

It seems one cant attach more than one DB table to a Flat file destination.

Error ''TITLE: Microsoft Visual Studio
--Cannot create connector.
--The destination component does not have any available inputs for use in
--creating a path.''

"Union all" & "Merge Join" options do apply to this data transfer.

Is there a Data Flow task that one can use to perform this operation.

Answer this question

Writing data from multiple tables to a single flat file

horseshoe
Hi Jamie.

I tried your theory but ran into a problem.

I added dummy fields to my tables like you reccommened.

I then used the "Copy Column" function and made a copy of the "Header Rec." and renamed the fields in the "Output Alias" of the copy column function to that of the field names in my "Detail Rec."

I then proceeded to use the join (Joining the Header and Detail Rec.) and mapped the necessary fields names to each other.This all worked.

The problem was that each row of data is of a fixed length. Each row is 313 charcters long, and certain columns from either record are not of the same length and contain different data thus resulting in the certain fields, trying to be truncated.

Thank you anyway

Jon vdB

Raj0001

Jon vdB wrote:
Hi Jamie.

I tried your theory but ran into a problem.

I added dummy fields to my tables like you reccommened.

I then used the "Copy Column" function and made a copy of the "Header Rec." and renamed the fields in the "Output Alias" of the copy column function to that of the field names in my "Detail Rec."

I then proceeded to use the join (Joining the Header and Detail Rec.) and mapped the necessary fields names to each other.This all worked.

The problem was that each row of data is of a fixed length. Each row is 313 charcters long, and certain columns from either record are not of the same length and contain different data thus resulting in the certain fields, trying to be truncated.

Thank you anyway

Jon vdB

Hi! I'm working on a similar solution. I may have missed it or misunderstood, but how did you resolve that your header record contained a different number of columns than your detail records and those columns having different lengths I'm having trouble getting the detail record columns to map to the same file as the header record columns. My header record has two columns (string with length 1, string with length 50). My detail records have 4 columns (string with length 20, string with length 10, string with length 5, string with length 50). The records need to be fixed length when written to the file. Can you or someone please help me with this I would greatly appreciate it!

Thanks,

Rebecca

David 111

Jon,

Why not just have 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. I tested this and it worked fine for me.

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

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

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

HTH,
Matt
manilahunk
The UNION component satisfies the basic premise of what you need to do however you're going to be hampered by the fact that the metadata of each source table is different. You may have to make them look the same, probably by renaming the 4 columns in the header and trailer to be the same as the first 4 columns in the detail, and then adding 24 NULL records to the end of the header and trailer.

Does that work

-Jamie
Donis Marshall
Hi Matt,

Thanks for the help. Your solution to the problem was spot on ;)

Regards

Jon vdB
Writing data from multiple tables to a single flat file

Answer this question