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.

Fabio

Can you post minimum code and text file which is required to reproduce the problem? See the help here – Riki

@Fabio it is good to read the Tour page – Aspirator

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.

Ichthyosaur

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.

Aspirator

Answer 3

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

Pic with example

It worked for me.

Rogozen

Answer 4

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.

Glaab

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Goeger

Answer 5

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.

Perilymph

This ought to be the accepted answer - this worked for me. I wish I could upvote this twice. – Binominal

Answer 6

Do not set the last column delimiter to LF. If the file row ends with {LF} (which I believe you have verified) then set the header row delimiter to {LF}:

Comp

the last column delimiter is always equal to the row delimiter. changing it can causes errors – Vandavandal

Answer 7

This seems to be more related to badly formatted delimiters in csv files. Hope the following threads will help:

How to replace double quotes in derived column transformation?

https://social.msdn.microsoft.com/Forums/en-US/fe6be73a-d0d5-4d41-8d09-db5fe32f0d68/instruct-dataflow-task-to-redirect-bad-input-rows-to-log-file?forum=sqlintegrationservices

Snatchy

Answer 8

I had the same issue, in my case the root cause is a space before the comma on the quote qualified field.

Example: "John","Smith" ,"NY" (There is a space after "Smith" and before comma)

Percival

Answer 9

I was using $ as text delimiter in the dat file. But, I changed the setting to none in the dat file. This caused the error to resolve.

Substitutive

Answer 10

In my case the error occurred when the text qualifier was present in the data. In this case it should be duplicated, for example value "ab"c" should be replaced with "ab""c".

Marcelenemarcelia

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Infraction