SSIS Text was truncated with status value 4

Question

I am developing a SSIS package, trying to update an existing SQL table from a CSV flat file. All of the columns are successfully updating except for one column. If I ignore this column on truncate, my package completes successfully. So I know this is a truncate problem and not error.

This column is empty for almost every row. However, there are a few rows where this field is 200-300 characters. My data conversion task identified this field as a DT_WSTR, but from what I've read elsewhere maybe this should be DT_NTEXT. I've tried both and I even set the DT_WSTR to 500. But none of this fixed my problem. How can I fix? What data type should this column be in my SQL table?

Error: 0xC02020A1 at Data Flow Task 1, Source - Berkeley812_csv [1]: Data conversion failed. The data conversion for column "Reason for Delay in Transition" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task 1, Source - Berkeley812_csv [1]: The "output column "Reason for Delay in Transition" (110)" failed because truncation occurred, and the truncation row disposition on "output column "Reason for Delay in Transition" (110)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task 1, Source - Berkeley812_csv [1]: An error occurred while processing file "D:\ftproot\LocalUser\RyanDaulton\Documents\Berkeley Demographics\Berkeley812.csv" on data row 758.

Suave

Answer 1

I suspect the or one or more characters had no match in the target code page part of the error.

If you remove the rows with values in that column, does it load? Can you identify, in other words, the rows which cause the package to fail? It could be the data is too long, or it could be that there's some funky character in there SQL Server doesn't like.

Gantz

Beth, like I said above when I ignored truncates for this one column then all of these records were updated. – Suave

OK, so you've looked at the rows and don't see anything unusual about them. It could be the length instead. Can you copy the data from one of the failing rows into the table manually? It might give you a more meaningful error message. – Gantz

How would I copy data from Excel to SQL for these failing rows? – Suave

Write an insert statement. The idea is to get it working outside of SSIS. SQL will probably give you a better message, or at least isolate the problem for testing. – Gantz

I'm sorry Beth; I know how to write an insert statement for SQL data but not for Excel. How can I do this? – Suave

You want data from the csv to go to the sql server, right? So you identify a row in the csv file causing the problem, then instead of inserting with ssis, insert with an sql statement using mgmt studio. Using Excel for the csv is ok, but Notepad should work, too, unless it's too big. – Gantz

The issue for me was the _or one or more characters had no match in the target code page part _, which I fixed, by setting the correct code page (the SSIS package I was using had defaulted to ascii, when I needed to use UTF-8). – Bega

Answer 2

One possible reason for this error is that your delimiter character (comma, semi-colon, pipe, whatever) actually appears in the data in one column. This can give very misleading error messages, often with the name of a totally different column.

One way to check this is to redirect the 'bad' rows to a separate file and then inspect them manually. Here's a brief explanation of how to do that:

http://redmondmag.com/articles/2010/04/12/log-error-rows-ssis.aspx

If that is indeed your problem, then the best solution is to fix the files at the source to quote the data values and/or use a different delimeter that isn't in the data.

Wrennie

Thanks, but my manager just informed me that we no longer need this table/file! – Suave

If you are using SSMS to export and then import the data, make sure you tell it to use quoted identifiers to make this error disappear. – Opec

Answer 3

I've had this issue before, it is likely that the default column size for the file is incorrect. It will put a default size of 50 characters but the data you are working with is larger. In the advanced settings for your data file, adjust the column size from 50 to the table's column size.

Es

Just a follow up to this, make sure you are setting this update on the source, not just on the target. I was getting that same exact error, during read I was getting the error not output to the table. – Carmina

Answer 4

If this is coming from SQL Server Import Wizard, try editing the definition of the column on the Data Source, it is 50 characters by default, but it can be longer.

Data Soruce -> Advanced -> Look at the column that goes in error -> change OutputColumnWidth to 200 and try again.

Paquette

Answer 5

I've had this problem before, you can go to "advanced" tab of "choose a data source" page and click on "suggested types" button, and set the "number of rows" as much as you want. after that, the type and text qualified are set to the true values.

i applied the above solution and can convert my data to SQL.

Serum

Answer 6

In my case, some of my rows didn't have the same number of columns as the header. Example, Header has 10 columns, and one of your rows has 8 or 9 columns. (Columns = Count number of you delimiter characters in each line)

Springer

Answer 7

If all other options have failed, trying recreating the data import task and/or the connection manager. If you've made any changes since the task was originally created, this can sometimes do the trick. I know it's the equivalent of rebooting, but, hey, if it works, it works.

Guillaume

Answer 8

I have same problem, and it is due to a column with very long data. When I map it, I changed it from DT_STR to Text_Stream, and it works

Downes

Answer 9

In the destination, in advanced, check that the length of the column is equal to the source.

Jalousie

Answer 10

OuputColumnWidth of column must be increased. Path: Source Connection manager-->Advanced-->OuputColumnWidth

Highhat

Welcome to Stackoverflow. This question is asked more than 12 years ago and it has an accepted answer. Please add some details about the reason you are adding a new answer. – Assiduous

Answer 11

In my case I was need to reset all columns in defenition of source flat file by pointing the actual file and then resetting all columns

Vagal