SSIS - Flat File Connection Manager Behavior when all the columns in a data row don't have entries

I hope some of you might not have faced with this feature of Flat File Source Connection Manager in SSIS, where Flat File Source cannot handle the delimited data if the column values are restricted to only some of the columns in each row.

Consider you have data in the file as below:

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

0123^4567^01^20171401^XXX^V1

0123^4567^01^20171401

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

When we create Flat File Connection Manager in SSIS, use above file data as source.

With Column Delimiter as “^ “and Row Delimiter as “{CR}{LF} “.

Once the configuration has been made and when you see Preview it shows only 3 Rows and not all the 4 Rows.

This behavior is because of the fact that SSIS Column Delimiter gets first preference and then Row Delimiter.

Consider our File Data Now : 

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

Totally there are 7 “^” s, So Connection Manager Expects 7 “^”s and the above row will be shown normally.

0123^4567^01^20171401^XXX^V1

In this row it finds only 5 “^”s, so it thinks the row is not yet over and continues till it finds two more “^” s. It will ignore the row delimiter as it is on its way to find two more “^”s.

0123^4567^01^20171401

Here it will append this row to previous row as it thinks previous row is incomplete and after two “^”s once it reaches 7^th “^”, It will append rest of the data in this row up to row delimiter to previous row.

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

For this row, everything is normal hence it will be shown correctly as one full row.

So if you do the preview with this file you will see as below now,

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

0123^4567^01^20171401^XXX^V10123^4567^01^20171401

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

In this case, what needs to be done is :-

 SET AlwaysCheckForRowDelimiters Property of connection manager to true (If it's not set by default) 

and you will see all rows behaving normally when you do the preview.

By default, the flat file connection manager always checks for a row delimiter in unquoted data and starts a new row when a row delimiter is found. This enables connection manager to correctly parse files with rows that are missing column fields.

In some cases, disabling this feature may improve the package performance. You can disable this feature by setting the flat file connection manager property, AlwaysCheckForRowDelimiters, to False.

This is not present in documentation till SQL 2008 R2 SSIS.

NOTE : If the file data is as above and AlwaysCheckForRowDelimiters Property is set to False Then the output data you will be getting same as in preview for most of the rows and the data flown across multiple steps in SSIS flow will go wrong.