Parse a flat file with multiple layouts to multiple destination tables?

Question

I am trying to load a flat file which mixed multiple data sets. The flat file looks like.

1999XX9999
2XXX99
1999XX9999
2XXX99
3XXXXX999.99
1999XX9999

The first character of the every row defines the record type of the line. I want to create a script component in data flow and parse the raw rows (as the below) and save three output (1, 2, 3) to three different tables. Is it possible?

Table1(col1, col2, col3):
999, XX, 9999
999, XX, 9999
999, XX, 9999

Table2(col1, col2):
XXX, 99
XXX, 99

Table3(col1, col2):
XXXXX, 999.99

Any other way in SSIS if script component cannot do it? The best solution is writing a program to split the file into three files and load them using SSIS?

Do you need to be able to know that that the second line came in with the first line or is really three unique files interleaved in one physical file? — billinkc
@billinkc yes, in face they are usually one to many relationship. — ca9163d9

Jeroen Bolle Jeroen Bolle · Accepted Answer · 2012-07-19T08:26:58

It is possible, and you probably should use a script transformation to create a maintainable solution.

You won't be able to completely parse your input file into columns using a flat file source and connection manager. Read your lines as full and use string functions in the script transformation to parse each line into the desired columns.

Now to distribute records to different destinations, you can either:

Define multiple outputs on your transformation and use a condition on the first character of each line to determine the output to which you send the columns.
Only use the script transformation to parse the line into columns and use a Conditional Split Transformation to logically divide your records over multiple data paths.

Both methods are logically similar, the implementation is different.

Parse a flat file with multiple layouts to multiple destination tables?

1 Answers