Sql-server – ‘skip rows’ in a flat file source does not work as advertised

sql-server-2012ssis

In SSIS(2012 specifically) there is an option in the Flat File Datasource to skip a number of lines at the beginning of a file. The preview appears correctly. However, when trying to execute the data flow, it appears that those rows are still being validated; so that if they contain invalid data(blank rows), the datasource fails. This prevents using a conditional switch to remove rows, since the data flow never starts.

Is this accurate description of the normal functioning and is there any way to work around it?

Sample data looks like –

COL1|COL2|COL3

abc|123|10
def|456|20
ghi|789|30

Error
Configuration

Best Answer

You're reading it backwards. You have instructed it to skip the first row of data and it does that. It then looks in the next row for column headers, which it won't find because it's blank. The actual data should be found on the third row. It tries to clue you in when you look at your preview. I create two files, one with your data and the other with the first line blank and the headers in the second row.

CM for blankfirstrow

Notice we are previewing rows 2-4 Columns for blankfirstrow

Contrast that with your blank in between header and data

enter image description here

Still previewing rows 2-4 but the default header names are the first rows of data. Not quite what we're expecting.

enter image description here

When I created a simple Data flow using the two connection managers, it fails (as expected) on the BlankSecondRow as the above definition performs some validation that the header names are actually there. If I uncheck "Column names in the first data row" and up the "Header Rows to Skip to" 2, then it will work.

enter image description here

Related Question