AlwaysCheckForRowDelimiters in SSIS

We have a file as below                      

  

We need to load this file into the database. See in this file we are getting irregular number of columns. While we are loading the data into the database it is not loaded in the correct format.

See this demo

We have below table 

Now we are taking source as flat file source and oledb destination and doing the mapping. 

Now we are running this package.   

Package executed successfully. It load 3 records only but in the file there are 5 rows.

Let’s see the records in the table.

 

It is not loaded in correct format.

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 we will see all rows behaving normally when we 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.

Currently AlwaysCheckForRowDelimiters (see the file connection manager property) is false due to that data is not loaded properly.

 

Now we are setting it true. 

Now we need to run this package.

Before running the package see the data in the table. 

Now running the package. 

Package executed successfully and 5 records are loaded into the table. 

See those column don’t have value for that the value will be null.