SSIS error: delimiter for column "columnX" is not found

Question

This question was asked here before but the solutions proposed don't seem to be working for me.

I'm trying to import a text file pipe delimited text qualifier ". The SSIS package is returning the error message

delimiter for column "columnX" is not found

where column X is the last field in the file.

In the connection manager advanced tab, I have set up the column delimiter for columnX to {LF} while all the other fields are set to vertical |.

I have verified the file and there are no missing fields. There are some null values but all the fields are there.

I can't point out what is causing this. Your help would be greatly appreciated.

Answer 1

I realize that this thread is old, but I was looking for a solution to this exact problem and did not find it. Deleting the flat file connection manager is extreme when I had so many columns that I adjusted the DataTypes for.

In my scenario with this error, it was the last column and the delimiter had switched from {CR}{LF} to {LF}. I switched it back to {CR}{LF} and it worked.

Also, under general, the Header row delimiter had also changed to {LF} and I changed it to {CR}{LF} too.

Answer 2

I suggest to delete the flat file connection manager, and recreate it so it will automatically set all options. Just select the column delimiter to |

If it doesn't works you have to verify that flat file does not contains bad rows.

Check these solutions they may helps:

• SSIS ragged file not recognized CRLF
• SSIS reading LF as terminator when its set as CRLF
• Import CSV File Error : Column Value containing column delimiter

Or if you have installed sql server try importibg this fla file to an sql server table using import and export wizard and check the data after importing it may helps detecting the lines containning errors.

Answer 3

Try to increase ColummnWidth in the Advanced tab on the Flat file Connection Manager Editor.

Pic with example

It worked for me.