76

I'm trying to import a flat file into an oledb target sql server database.

here's the field that's giving me trouble:

enter image description here

here are the properties of that flat file connection, specifically the field:

enter image description here

here's the 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.".

What am I doing wrong?

Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • 2
    "Text was truncated..." seems to infer that the imported file might have a value in that column with more than 100 characters... worth a look, at least. – Conduit Aug 28 '14 at 17:18
  • 4
    nope that's not the case, i did check – Alex Gordon Aug 28 '14 at 17:34
  • If you go into the Advanced Properties section of each component in the data flow, can you confirm that all of them correctly use the 100-character length? – AHiggins Aug 28 '14 at 17:41
  • That column in your flat file contains some weird characters that aren't recognized by the code page that you are using for your SQL destination. See if this helps: http://stackoverflow.com/questions/18360556/ssis-text-was-truncated-or-one-or-more-characters-had-no-match-in-the-target-c?rq=1 – Tab Alleman Aug 28 '14 at 17:54
  • I would try nvarchar(max) & see what happens – BI Dude Aug 28 '14 at 19:17
  • 2
    Redirect the rows to an error output and send it to a derived column or something that you do not need to configure. Then add a data viewer and try to find the rows with errors. It could be that some other column has a tab in it, which is throwing things off. Or maybe the previous line was not ended properly – Mark Wojciechowicz Aug 29 '14 at 15:51
  • @MarkWojciechowicz thank you. what do yoyu mean by data viewer? is that a control? – Alex Gordon Aug 31 '14 at 12:27
  • @yuck a data viewer is a tool that allows you to view data during debug. Right click the arrow between two components and select add data viewer (in this case, the error flow). Then run the package. A window will pop up when rows are sent to that buffer – Mark Wojciechowicz Sep 01 '14 at 15:06

12 Answers12

97

Here is what fixed the problem for me. I did not have to convert to Excel. Just modified the DataType when choosing the data source to "text stream" (Figure 1). You can also check the "Edit Mappings" dialog to verify the change to the size (Figure 2).

Figure 1

enter image description here

Figure 2

enter image description here

ADH
  • 2,971
  • 6
  • 34
  • 53
  • NOTE: I had to both update the data type to VARCHAR(MAX) first and also go to the advance screen and update the import to 5000 characters. – Nahuatl_C137 Mar 02 '19 at 14:34
  • 2
    Worked for me but also had to change the Code page from `1252 (ANSI - Latin I)` to `65001 (UTF-8)` on the Flat File Source step otherwise it was still failing with the same error. After trying a range of solutions to import over 1 million rows (so couldn't use Excel) this worked fine in combination with the above solution to switch to text stream. – Robin Wilson Jul 04 '20 at 17:25
34

After failing by increasing the length or even changing to data type text, I solved this by 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.

TylerH
  • 20,799
  • 66
  • 75
  • 101
  • 5
    What do you do when your data is too big for Excel? – BallpointBen Jan 07 '19 at 17:05
  • 1
    Converting the data type to nvarchar(255) didn't work. I am still getting the exception. I inserted using SQL and it works like a charm and length of string is only 51 characters. – Arjun Menon Oct 02 '20 at 17:58
  • Instead of using import data, I chose import flat file and it worked without any issue. One thing I noticed is that the column where I had issue , when using import data option, the column size was 50 ( content size was 51) and when using import flat file the column size was set at 100 – Arjun Menon Oct 02 '20 at 18:18
20

I solved this problem by ORDERING my source data (xls, csv, whatever) such that the longest text values on at the top of the file. Excel is great. use the LEN() function on your challenging column. Order by that length value with the longest value on top of your dataset. Save. Try the import again.

Eric Hrnicek
  • 201
  • 2
  • 2
7

SQL Server may be able to suggest the right data type for you (even when it does not choose the right type by default) - clicking the "Suggest Types" button (shown in your screenshot above) allows you to have SQL Server scan the source and suggest a data type for the field that's throwing an error. In my case, choosing to scan 20000 rows to generate the suggestions, and using the resulting suggested data type, fixed the issue.

user756366
  • 467
  • 6
  • 24
7

While an approach proposed above (@chookoos, here in this q&a convert to Excel workbook) and import resolves those kinds of issues, this solution this solution in another q&a is excellent because you can stay with your csv or tsv or txt file, and perfom the necessary fine tuning without creating a Microsoft product related solution enter image description here enter image description here

Albert
  • 356
  • 4
  • 7
6

I've resolved it by checking the 'UNICODE'checkbox. Click on below Image link:

Image

Community
  • 1
  • 1
Anand Bhosekar
  • 69
  • 1
  • 1
6

You need to go increase the column length while importing the data for particular column.

Choose a data source >> Advanced >> increase the column from default 50 to 200 or more.

TylerH
  • 20,799
  • 66
  • 75
  • 101
rahul pareek
  • 131
  • 2
  • 2
5

Not really a technical solution, but SQL Server 2017 flat file import is totally revamped, and imported my large-ish file with 5 clicks, handled encoding / field length issues without any input from me

enter image description here

Matt Evans
  • 7,113
  • 7
  • 32
  • 64
  • I imported a 3 millions records file, and it doesn't have an identity column. I want to add an identity column, can you show me how? – haiduong87 Mar 21 '19 at 08:46
  • Does this help? https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column – Matt Evans Mar 22 '19 at 06:56
3

SQl Management Studio data import looks at the first few rows to determine source data specs..

shift your records around so that the longest text is at top.

Pinch
  • 4,009
  • 8
  • 40
  • 60
1

None of the above worked for me. I SOLVED my problem by saving my source data (save as) Excel file as a single xls Worksheet Excel 5.0/95 and imported without column headings. Also, I created the table in advance and mapped manually instead of letting SQL create the table.

TonyE
  • 319
  • 2
  • 4
1

I had similar problem against 2 different databases (DB2 and SQL), finally I solved it by using CAST in the source query from DB2. I also take advantage of using a query by adapting the source column to varchar and avoiding the useless blank spaces: 

CAST(RTRIM(LTRIM(COLUMN_NAME)) AS VARCHAR(60) CCSID UNICODE 
   FOR SBCS DATA)  COLUMN_NAME

The important issue here is the CCSID conversion.

Bimal Das
  • 1,882
  • 5
  • 28
  • 53
JossWs
  • 11
  • 3
1

It usually because in connection manager it may be still of 50 char , hence I have resolved the problem by going to Connection Manager--> Advanced and then change to 100 or may be 1000 if its big enough

user11791703
  • 11
  • 1