4

I m trying to import file from Excel into an SQL table. When i convert from unicode string[DT_WSTR] to string[DT_STR] with data conversion i get a truncation error on some columns. This is output error:

[Data Conversion [2]] Error: Data conversion failed while converting column "Contact Name" (187) to column "DataContactName" (105). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

So data flow finish as failure, but some rows are copied to sql table. Is there some workaround or what is solution to this?

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
DraganB
  • 1,128
  • 2
  • 10
  • 26
  • 2
    That depends on what you want the solution to be. Do you want to ignore the error because you're fine with the conversion/truncation, or do you want to know how to change your target schema to avoid the errors, or do you want to make sure either all rows are copied or none of them are? – Jeroen Mostert Jun 19 '17 at 13:05

2 Answers2

4

Here's what you can do

  1. If you are sure that your conversion is correct and Excel does not contain any characters which are lost while conversion to non-unicode/ASCII text, thus not resulting in truncation, change your failure setting from Failure on error to Ignore on error. Though you should definitely output the error and log to a flat file for checking the data lost.
  2. Check the data table's column; is it sufficient to hold every possible excel file value? If you are not sure, then try converting the table column to VARCHAR(MAX). You should not get truncation error now, if it was due to size. Other way is to simply choose a destination size for VARCHAR(N) where N=2*maximum length of the excel column.

On your comment :

I added ignore truncation errors in error output and all rows were copied in sql table. There was no problem to convert characters like ; or "á". Can u brief me what kind of errors i ignored?

I suggest that you use a Multi-cast transformation on the error output after changing the failure setting to from Ignore Failure to Redirect Row. Then insert one of the output from Multi-cast into SQL server and other on a flat file destination. This way you'll get data into SQL server as well in flat file to analyse the error

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • I added ignore truncation errors in error output and all rows were copied in sql table. There was no problem to convert characters like ; or "á". Can u brief me what kind of errors i ignored? Thank you a lot. – DraganB Jun 19 '17 at 13:45
1

Looks like the length defined for DataContactName in data conversion is less than the length of "Contact Name". Check the max length of the column "Contact Name" and set DataContactName size based on that. You are seeing few rows inserted because they might have been inserted before the data with greater length arrived.

Shareef
  • 86
  • 5
  • I ignored erros and all rows were copied to sql. I checked, there was no problem with character conversion, i dont know what were the errors. In future should i use DT_TEXT instead of [DT_STR]? – DraganB Jun 19 '17 at 13:49