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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 - gt  Advanced - gt  Look at the column that goes in error - gt  change OutputColumnWidth to 200 and try again

User · Answer

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

User · Answer

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

[text] SSIS Text was truncated with status value 4

Examples related to text

Examples related to csv

Examples related to ssis