SSIS Flat file connection

Question

I am getting a flat file as given below. I have been using ssis to load this data. It has 9 dynamic column name and only first three columns has the row data and rest don't have any data.

In the flat file connection manager. I have selected:

• row delimiter as {CR}{LF}
• Column delimiter as Comma {,}

File_content:

S_NO,FIL_NO,PN,DATE,RT_NO,MW_NO,RS_NO,MIS49,TMIS
2,61,HZZ
2,62,HZZ1
2,63,HZZ2
2,64,HZZ
2,65,HZZ1
2,66,HZZ2

When I run the package, the data has written as like below

S_NO    FIL_NO  PN  DATE    RT_NO   MW_NO   RS_NO   MIS49   TMIS
2         61    HZZ     2   63      HZZ2    2         65    HZZ1
2         62    HZZ1    2   64      HZZ     2         66    HZZ2

Answer 1

Referring to the Flat File Connection Manager official documentation:

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 the connection manager to correctly parse files with rows that are missing column fields.

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

In order to fix this situation, make sure that AlwaysCheckForRowDelimiters property is set to True.