Another SSIS Gotcha!

Here is another difference between DTS & SSIS that will trip you up if you are not expecting it.  The issue deals with loading flat files with varying numbers of columns.  For example the 1st row has 5 columns, then the 2d row has 3 columns, and then the 3d row has 4 columns.  DTS handled this by always looking for the row delimiter first, whereas SSIS expects to always have the same number of column delimiters in each row.  Here are some great posts that can explain this better than I can:

http://blogs.conchango.com/jamiethomson/archive/2006/07/14/SSIS-Nugget_3A00_-Extracting-data-from-unstructured-files.aspx

http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_–Flat-File-Connection-Manager-issues.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx