Post reply

SQL2000 vs SQL2005 Data Truncated

  • December 17, 2008 at 4:07 am

    #128372

    Structure

    SSIS(DTS) importing flat file

    Data Im working with:

    VARCHAR, 16

    Example Data:

    5.39275018181818

    SQL 2000

    This process works file imports correctly

    SQL 2005

    Errors:

    Error: 0xC02020A1 at ImportFiles, Bargains [3864]: Data conversion failed.

    The data conversion for column "Column 4" returned status value 4 and status text

    "Text was truncated or one or more characters had no match in the target code page.".

    Question

    How do i tell my SSIS package, truncate data longer than 16...?

  • December 17, 2008 at 4:10 am

    #913681

    Add a dataconversion transofrmation into your data-flow and use this to truncate your column before you insert the data into the destination table.

  • December 17, 2008 at 4:22 am

    #913686

    Thanks,

    I have:

    1. added the DC betweent he flat file and the OLE DB.

    2. Selected the column needed

    I cannot seen how to truncate the data?

  • December 17, 2008 at 4:41 am

    #913698

    In the data conversion properties,

    Select the input column, give this coulmn an output alias.

    Select the data-type (eg. string [DT_STR])

    Set the Length to the same length as in your table

    click configure error output..

    and set the tuncation error for the column, to either ignore (this will cut the overflow data out) or

    redirect-row (you can use this to send the rows to an error log which you can review the problem data)

    Now use the new truncated output alias column in rest of your data-flow in place of the orginal column.

  • December 17, 2008 at 4:54 am

    #913705

    Thanks for the detailed information.

    I have made the changes you have suggeste dand Mapped the OLE DM Column to the New Alis.

    I am still getting the same error.

    Is there anything I need to do witin the FlatFile Connection Manager?

    p.s. I set set the tuncation error for the column, to ignore

    Thanks

  • December 17, 2008 at 7:57 am

    #913841

    Does the FlatFile Manager need to be changed?

  • December 17, 2008 at 8:40 am

    #913898

    The flatfile manager may need to be changed,

    go into the advanced settings and select the column that is casuing the issues. set the length of this column to the maximum length in the flat file, this way all the data will be loaded and your data conversion transformation will deal with all the data type issues

  • December 17, 2008 at 8:54 am

    #913919

    Thank you very very much that worked well!

    thanks for your time

  • December 17, 2008 at 8:57 am

    #913921

    no worries, glad it worked...

Viewing 9 posts - 1 through 8 (of 8 total)

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