none
Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or on

    Question

  • Hi Experts,

     

    I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".

     

    The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.

     

    If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.

     

    I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.

     

    Can anyone suggest how to solve this issue.

    Monday, January 07, 2008 11:58 AM

Answers

  • Try this as well:

    go to the properties of the source and select 'show advanced editor'.

    go to tab 'input and output properties'

    expand the tree node 'oledb souce output'

    expand the child node 'output columns'

    click on the specific column that gives you trouble.

    when you click on the column, the properties of the column will be displayed on the right side. Go to property 'TruncationRowDisposition' and change the value to 'RD_IgnoreFailure'.

    Monday, January 07, 2008 1:05 PM
  • If I use a Derived Column, and a SUBSTRING, for adding a new column, then the output type is the length of the substring output, not the wider input I used.

     

    You mention building this via code, have you tried this Derived Column just in the designer for practice? Perhaps create your package in code, then save it, and open it in the designer, then compare it to a designer built package.

     

    I'd get it working in the designer first, then move to code.

     

    For doing the ignore in code, look for the TruncationRowDisposition property on the output column. You should not need to do this though, a SUBSTRING will shorten the column, so no truncation is required. I dislike ignore because it hides all errors, the unexpected as well as the expected.

    Monday, January 07, 2008 6:26 PM

All replies

  • Once in SSIS however, you should be able to cast columns to new data types. Note, changing the size will require a new column, so a Derived Column will need to be set to add as new column rather than replace abc. The Data Conversion always creates a new column, but you of course need to use then new column in the destination, not the original.

     

    Once setup it can be difficult to get column size changes to be recognised in downstream components, things are not always as clever as they seem, so a bit of tweaking with the advanced editor maybe required (right click the component, Show Advanced Editor), or it may just b quicker to re-create the destination.

    Monday, January 07, 2008 12:32 PM
  • Try this as well:

    go to the properties of the source and select 'show advanced editor'.

    go to tab 'input and output properties'

    expand the tree node 'oledb souce output'

    expand the child node 'output columns'

    click on the specific column that gives you trouble.

    when you click on the column, the properties of the column will be displayed on the right side. Go to property 'TruncationRowDisposition' and change the value to 'RD_IgnoreFailure'.

    Monday, January 07, 2008 1:05 PM

  • Thank you Darren & Jacob.
    I will try these options.

    Darren, I added a new column and then used Substring function as well still i am getting the same error in the Derived column component.

    Jacob i will try this. However I am creating the package programatically. Will I be able to set this progamatically? If so can you give me examples.
    Monday, January 07, 2008 5:16 PM
  • I have not tried to do it programatically. I guess there might be a way. I do not have a clue that I can give you on this.

    Monday, January 07, 2008 5:28 PM
  • If I use a Derived Column, and a SUBSTRING, for adding a new column, then the output type is the length of the substring output, not the wider input I used.

     

    You mention building this via code, have you tried this Derived Column just in the designer for practice? Perhaps create your package in code, then save it, and open it in the designer, then compare it to a designer built package.

     

    I'd get it working in the designer first, then move to code.

     

    For doing the ignore in code, look for the TruncationRowDisposition property on the output column. You should not need to do this though, a SUBSTRING will shorten the column, so no truncation is required. I dislike ignore because it hides all errors, the unexpected as well as the expected.

    Monday, January 07, 2008 6:26 PM
  • I'm getting the same error but am trying to import from a flat tab-delimited text file in SQL Server 2005 Express.  I have the field set as a varchar 200.  The row the error points to is well below 200 characters and I don't see anything that it would interpret as a stop.  The rows have data resembling the data in the row that is failing.

     

    Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 3" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
    (SQL Server Import and Export Wizard)

    Thursday, April 24, 2008 3:13 PM
  • Here you go! Thanks a lot, It works for me.

    Monday, December 01, 2008 11:48 PM
  • Jacob -

    Your suggestion worked like a charm for me.  I did just what you said by ignoring the failures for those "problem columns" and my package executed w/o a problem.

    Monday, January 03, 2011 7:35 PM
  • to the point. Thank you Jacob.

    Tuesday, October 22, 2013 4:00 PM