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

user_8111898 0
Viewed 32789

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.

ssis-2012 sql-server-data-tools etl ssis sql
7 Answers

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.

user_109022120

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

Pic with example

It worked for me.

user_61274340
user_30561600

This has happened to me on several occasions. Each time it has been caused by someone adding a column to the csv file. The error happens when it gets to the end of the last column and expects a carriage return line feed cr/lf, but finds only the carriage return because there is an added column.

user_181720290

Ran into this on some CSVs I had exported from Oracle SQL Developer and needed to import into SQL Server.

After many failed attempts to import my problematic CSV, I discovered a quirk - For some un-god-known reason, the header row terminated with CRLF while the data rows terminated with LF. Doing a mass replace of carriage returns in Notepad++ before importing allowed me to proceed. SSIS seems to have difficulty importing flat files with mixed line endings.

One other callout in case it helps some other poor soul - if you're importing a max length varchar column, you may need to change the data type to Text Stream in order for SSIS/Import Wizard to process it.

user_59595690
Related