Strange/Inconsistent SSIS row import error

Question

I'm importing a csv non-unicode file using SSIS into SQL Server. I get the error "Text was truncated or one or more characters had no match in the target code page". It fails in column 0 on row 70962, which has data just like every other row; the data in the first column is no longer than the data in rows above it.

My column 0 is defined in the flat file connection, and in the database, as 255 wide. The data in row 70692 (and most other rows) is 17 characters.

The strange thing is, if I remove a row above row 70962 in the file, even the first row, and save the csv file, then the import runs fine. If I replace that removed row, and run the import, it fails again.

So I'm not even sure how to identify what the issue is.

If I create a new flat file connection that is a single column, I can import the whole file into a single-column table. But as soon as I add the first column delimiter (i.e. second column), then it fails on that same row.

At the moment I'm just short of idea as to how to debug this further.

Answer 1

You already gave the answer in your question ;)

if I remove a row above row 70962 in the file, even the first row, and save the csv file, then the import runs fine.

You have a broken delimiter somewhere in the file. when you remove any data before the offending line the mismatch of delimiters is probably not properly handled but simply left open until the very end of the file after which the program handles it for you.

Check the row and column delimiters of the row above the one you mentioned and that very row.