SSIS Flat-file source - Text was truncated or one or more characters had no match in the target code page

4
votes

I'm trying to import records into an SQL database file from a comma-delimited flat file and I get a "Text was truncated or one or more characters had no match in the target code page." when the source file has more than 50 characters in a particular field. The Destination file target for that field is 1000 characters, so that shouldn't be an issue. I narrowed it down to "A123456789B123456789C123456789D123456789E123456789" in the text file did import while "A123456789B123456789C123456789D123456789E123456789F" threw the error.

How can I get it to allow more characters before truncating?

4
ssis

4 Answers

9
votes

I found the answer in the second answer to: Importing CSV file into SQL server... Thanks to Eoan for that.

Ultimately, in the Advanced Editor of the source datafile, on the Input and Output Properties tab, under External Columns, there is a Length property that defaults to 50. Changing that to match the Target Database File did the trick.

1
votes

Another quick solution:

If you have Microsoft Excel, save the CSV as XLSX. And, import the XLSX instead of CSV I found that import wizard understands Excel much better than CSV.

0
votes

Even if I'm importing an excel file, I'm also having problems with text being truncated. I found out that

the SSIS system determines the data type of an EXCEL column by examining the first 8 or so rows (ridiculous). So if all of the first 8 rows are less than 255 chars, and after the 8th row there are values greater than 255 chars, the truncation error will occur.

So the workaround is to throw in a temporary row with a large (> 255 chars) value in the problematic column, and then run the Integration. Once complete, delete the temporary row. -ryan1999

(see answer here)

My work around is:

  1. add another column containing the length of the column that will supposedly be truncated
  2. sort that column from largest to smallest
  3. delete the column.

Hope this helps someone

0
votes

In my case -- moved the offending row as the first one (if you can since csv/text files can be quite LARGE).

  • Run the preview.
  • Turns out the field had a comma in there, but thankfully it had double quotes showing separation of fields.
  • Went to 'Choose a Data Source" dialog > General, added Text qualifier: " (double-quotes)