CODEWITHSUNDEEP
sqlssisetlsql-server-data-toolsssis-2012
marcia12
marcia12

Reputation: 169

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

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.

Upvotes: 8

Views: 45594

Answers (10)

SKK
SKK

Reputation: 43

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".

Upvotes: 0

Benzi
Benzi

Reputation: 458

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

Pic with example

It worked for me.

Upvotes: 1

Indrashish
Indrashish

Reputation: 1

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.

Upvotes: 0

Habesha
Habesha

Reputation: 495

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)

Upvotes: 0

ke1v3y
ke1v3y

Reputation: 57

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.

Upvotes: 1

Terry Riley
Terry Riley

Reputation: 11

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.

Upvotes: 1

rchacko
rchacko

Reputation: 2139

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

Upvotes: 0

Fabucci
Fabucci

Reputation: 91

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.

Upvotes: 8

Hadi
Hadi

Reputation: 37368

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:

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.

Upvotes: 1

Arthur
Arthur

Reputation: 1469

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}: enter image description here

Upvotes: 0

Related Questions