• Register
0 votes
504 views

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?

6 5 3
7,540 points

Please log in or register to answer this question.

2 Answers

0 votes

Solution :

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.

9 7 4
38,600 points
0 votes

Solution:

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.

10 6 4
31,120 points

Related questions

0 votes
1 answer 17 views
0 votes
1 answer 197 views
197 views
Problem : My stored procedure giving me following error cannot perform an aggregate function on an expression containing an aggregate or a subquery.
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
1 answer 8 views
8 views
Problem I am working on some query for my project and the requirement is to show the second value. For sake of example, say I have salary table in Microsoft SQL Server and I need fetch the second maximum salary. I am not too experienced in T-SQL, I used the ... So I sow there is RANK() function in T-SQL, but I do not completely understand how it works. Any suggestions will very appreciated.
asked Dec 17, 2020 alexh 2.2k points
0 votes
1 answer 9 views
0 votes
1 answer 7 views
0 votes
1 answer 9 views
0 votes
1 answer 36 views
1 vote
1 answer 127 views
127 views
Problem : I am very new to SQL. If I try to use the sp_executesq I am often facing below strange looking error: “Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.” I have spent more than two days on above error but still can not fix above sql related error. Please guide me in fixing above sql error.
asked May 12, 2020 Martin K 6.6k points
0 votes
1 answer 451 views
451 views
Problem : I have the table storing starttime and endtime. I am facing below error: “Conversion failed when converting character string to smalldatetime data type.” While selecting the below query using that table. select CAST(substring(CONVERT(varchar,convert(smalldatetime,o. ... face any error. But, when I select it from a table I got the error. Can somebody help me with this?
asked Jan 20, 2020 jwilliam 3.9k points