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.
