Post reply

Data type conversion error

  • February 18, 2020 at 11:00 am

    #3726679

    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

     

  • February 18, 2020 at 11:22 am

    #3726683

    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

  • February 18, 2020 at 11:39 am

    #3726689

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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • February 18, 2020 at 11:55 am

    #3726695

    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.

  • February 18, 2020 at 12:27 pm

    #3726700

    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, 9 months 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