SSIS package - extract data from first n rows and import data from n+1th row from a flat file

1
votes

I have a flat file with the following structure (first 3 lines are information about the file content and data starts at 4th row):

ImportSourceId,ReadTime,Location
ColumnHeader1,ColumnHeader2,ColumnHeader3,ColumnHeader4,ColumnHeader5,ColumnHeader6
Unit1,Unit2,Unit3,Unit4,Unit5,Unit6
DataForColumn1,DataForColumn2,DataForColumn3,DataForColumn4,DataForColumn5,DataForColumn6

I would appreciate suggestions to import this data to a target SQL Server table using SSIS. I am thinking on these lines:

  1. Add a connection manager. 3 columns will be created based on the number of values in first row (ColumnHeader3 thro ColumnHeader6 are all being treated as one column by the connection manager at this point). As I want to extract information from the first row, I can't set 'Header Rows To skip' (?).
  2. Add a script component to read first 3 rows to a string variable and extract the data as required.
  3. (not sure how to split the 3rd column to 3 columns at this point)

Regards, Mohan.

1
ssis

1 Answers

0
votes

Assuming the column names are always static:

When importing the file, use a flat file connection.

  1. Skip the first 3 rows with "Header Rows to skip"
  2. Uncheck "column names in first row"
  3. Click "Advanced" and manually set your column names.