Data type conversion error

  • All,

    This is my data flow:

    Overview

    I'm getting the error:

    "Error: 0xC02020A1 at Data Flow Task, Flat File Destination 1 [83]: Data conversion failed. The data conversion for column "TitleAnsi" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page"

    The conversion is setup as follows:

    SSIS Conversion

    The file destination is configured as follows:

    SSIS file destination

    I would appreciate some help in trying to work out why the conversion isn't identifying the problem rows and sending them to the error output? I don't think it's relevant but the error output is configured as follows:

    SSIS error destination

    I wondered if there could be a different code page between the conversion and the file output? However I can't set the code page on the conversion?

    Thanks

     

  • The error is in the Flat File, not the Data Conversion Transformation. Seems like you've defined the column to be smaller than 250 characters in there. By default, a Flat File's String data type has a length of 50 characters in SSIS (if I recall correctly).

    I would check your Connection Manager for the file, and check the column definitions.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Agree with Thom. Redirect your errors from the target if you are having trouble tracking this down.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks for your help. Sorry I was thinking that the problem was with the original data so the conversion task should identify it before it reached the flat file.

    I've just changed the output type on the conversion task to string and the error has gone so it seems to have been a mismatch between the data conversion definition and the flat file definition. I attached the flat file definition anyway as I'd already made the screenshot.

    Thanks for your help and making me realise I was looking at the wrong reason for the error.

     

    Attachments:
    You must be logged in to view attached files.
  • Ahh, there we go. Your column, in the Flat File, is defined as string (DT_STR) not unicode string (DT_WSTR) as a result you get the error you got, as you were trying to insert a unicode string into an ANSI datatype. You were explicitly converting to DT_WSTR in your Data Conversion Transformation. SSIS requires very strict typing, and it will not implicity cast anything.

    Note, the clue for where the error was was actually in the error:

    Error: 0xC02020A1 at Data Flow Task, Flat File Destination 1 [83]: Data conversion failed. The data conversion for column "TitleAnsi" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page

    The errors always tell you the node the error occured at. 🙂

    I should have clued in with a columns called "TitleAnsi", which was being explicitly converted to Unicode. ¯\_(?)_/¯

    • This reply was modified 4 years, 1 month ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply