Mar 9, 2011

SSIS - Script Component, add missing column or ignore extra columns

Today I got a query asking me if we can have a script component which reads data from a text file which is supposed to have 5 input columns. However the data that they receive has at time extra columns or less columns for certain records. So the question arises how do we go about this.
1.Col4    Col3    Col2    Col1    Col
2.5   4   3   2   1
3.5   4   3   2   1
4.NULL    NULL    3   2   1
There are 3 ways that come to my mind straight away.
  1. Script Component
  2. Flat file source which reads all data into one column and then splits them based on index of the comma in a derived column
  3. Flat file source with delimited settings to read just 5 columns.