SSIS Flat file connection

2
votes

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
1
csvssisetlflat-fileflatfilesource
Check the following post: stackoverflow.com/questions/56942190/… – Hadi
What is your goal? To me, it seems like skipping header row might be your easiest option and then defining the columns in advanced editor. The csv is really invalid as the detail records should have "," for missing columns. for example: 2,61,HZZ,,,,, – KeithL
My goal is to load this file as the column changes dynamically every time.This is source file we receive from the third party.the file contains 8 or 9 columns but the it has the data only for first 3 columns and rest of the cols are empty – MSM

1 Answers

0
votes

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.