8

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
marcia12
  • 159
  • 1
  • 2
  • 12
  • Can you post minimum code and text file which is required to reproduce the problem? See the help [here](https://stackoverflow.com/help/mcve) – Nef10 Jun 05 '17 at 00:50
  • @marcia12 it is good to read the [Tour page](http://www.stackoverflow.com/tour) – Hadi Jun 05 '17 at 10:53

8 Answers8

8

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.

Fabucci
  • 91
  • 1
  • 5
1

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
1

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

Pic with example

It worked for me.

Benzi
  • 398
  • 6
  • 14
1

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.

Terry Riley
  • 11
  • 1
  • 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](/help/how-to-answer). – Community Feb 10 '22 at 16:02
1

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.

ke1v3y
  • 47
  • 4
0

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

Arthur
  • 1,441
  • 1
  • 13
  • 17
0

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

rchacko
  • 1,965
  • 23
  • 24
0

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)

Habesha
  • 465
  • 5
  • 9