What happens when SSIS receives extra columns in a flat file source? Badness...

2014-09-03
Mark Wojciechowicz

I was working on a truncation error in a package, today, which stemmed from a flat file source.  What I found was much more scary - we had been processing data incorrectly for months!  Some of the rows in our data source contained more columns than expected.  However, rather than failing, SSIS gracefully swept the additional columns into the last column and continued without error.

I will demonstrate the problem with a simple example.  Working with a test file, suppose I configure a flat file connection manager which has three columns:



Then I set up a flat file source and a data viewer like so:


If I then change the flat file source and a forth column to one of the rows, like this:
Column1 Column2 Column3
Data1,Data2,Data3
Data1,Data2,Data3,Data4

This is what happens:


This error was found in 2008R2, but it is still present in SQL 2014.

Please give this a vote on connect!



Labels: