Text was truncated or one or more characters had no match in the target code page

Question

Problem :

I want to import the flat file into my oledb target sql server database.

But I am getting below error message :

[Source - 18942979103_txt [424]] Error: Data conversion failed. The data conversion for column "recipient-name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Why am I getting this error message? What am I doing wrong here?

Answer 1

I tried to increase the length and also tried to change the data type to text but it was not working for me. But after creating an XLSX file and importing it worked for me. It accurately detected my data type instead of setting all columns as varchar(50). The nvarchar(255) for my column would have worked too.

OR

I solved my problem by just ORDERING the source data (xls, csv, whatever) in such a way that my longest text values kept on the top of my file. Excel approach is great. You can use the LEN() function on your challenging column. You can order by that length value with the longest value on top of the dataset. Save it. And just try the import again.

It worked for me, hope it works for you too.

Answer 2

I know this is an old question. The way I solved it - after failing by increasing the length or even changing to data type text - was creating an XLSX file and importing. It accurately detected the data type instead of setting all columns as varchar(50). Turns out nvarchar(255) for that column would have done it too.

1. On the OLE DB Source Editor - Error Output - Choose "Redirect Row" on the Column that is causing the failure
2. Add a new Flat File Destination text file to store the redirected rows
3. On the Data Flow Path Editor leading to the main output file - Data Viewers - Add a new OLE DB Source Output Data Viewer (Grid). Ensure the issue column is in the Displayed Columns list

At this point the SSIS package is now configured to re-direct any failed rows to a separate error log file.
Also enabled is a Debug Visual of the rows as they are loading (Data Viewer).

The issue with this package was only 4000 of the ~70000 rows were being sent to the main text output file. Checking this output file shows a partially loaded row at the end of the file. Querying the db showed a number of invalid characters in that row.
Remove the invalid characters or exclude that row from the results set to get the SSIS working again.

Another Method

Convert all the char or varchar fields in the SELECT list in your stored procedure to nvarchar()
Then change the SSIS Flat File Connection Manager Code Page to "65001 (UTF-8)".
Then ensure the SSIS Data Flow Path Editor..Metadata..Code Page = 0 (not 1252 which is ANSI))
This makes the db return Unicode data and the output file is also Unicode/UTF-8
(UTF-8 is a character encoding capable of encoding all possible characters, or code points, in Unicode. The encoding is variable-length and uses 8-bit code units)

Also, confirm your output file us indeed in UTF-8 encoding. Open the file in Notepad, click 'Save As...', in the 'Encoding:' box you will see the current file format.

Or you can follw below steps

1. When the "Choose a Data Source" window pops up,
2. On the left-hand side, there is a column called "Advanced". (The wizard only allows you to click on "Advanced" when there is a Data Source selected already)
3. Click on the Advanced, another window shows up, on the right hand-side there is a column called "Msc",
4. Change the DataType in the dropdown menu to "text stream [DT_TEXT]".

N.B: This error happens when you have created a table with different columns containing different Data Type and you try to import data mapped to the column with different Data Type. In the video below, you will see the demonstration to attemp to import data to a table with changed column data type.