CODEWITHSUNDEEP
ssis
ca9163d9
ca9163d9

Reputation: 29159

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

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?

Upvotes: 0

Views: 1376

Answers (1)

Jeroen Bolle
Jeroen Bolle

Reputation: 1836

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:

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

Upvotes: 2

Related Questions