Post reply

Derived Column Error

  • August 29, 2012 at 4:52 am

    #280891

    I'm trying to "change" the data of a specific column during a SSIS process.

    I have a text file, that I'm trying to process into a database, and one of the columns (in the text file) contains "numeric" values or an empty space or null value or "--" (two dashes). I want to "replace" the empty space or "--" with a null value (DT_I4) and convert the numeric value to an integer (DT-I4) since the column in the data base is an int field.

    This is the code that I have in the expression field

    civilianYears == "--" || civilianYears == " " ? NULL(DT_I4) : (DT_I4)civilianYears

    However, when I run the task it keeps giving me an error message: The "Derived Column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[New]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

    I have a feeling it's the conversion in the "true" and "false" part but I can't figure out what it is.

    Any assistance would be greatly appreciated

  • August 29, 2012 at 9:41 pm

    #1531709

    A couple thoughts...perhaps the empty space string contains more (or less than one space) and also what happens when the souce column is null ?

    Perhaps try....

    civilianYears == "--" || TRIM(civilianYears) == "" || ISNULL (civilianYears) ? NULL(DT_I4) : (DT_I4)civilianYears

  • August 29, 2012 at 10:17 pm

    #1531710

    Hi

    try like below

    (civilianYears == "--" || TRIM(civilianYears) == "" || ISNULL (civilianYears)) ? NULL(DT_I4) : (DT_I4)civilianYears

  • August 30, 2012 at 1:42 am

    #1531743

    Nothing wrong with the previous two suggestions, but I rather prefer to check for NULL first, and TRIM() the column when checking for the double dashes, like below:

    (ISNULL (civilianYears) || TRIM(civilianYears) == "--" || TRIM(civilianYears) == "") ? NULL(DT_I4) : (DT_I4)civilianYears

    Cheers,

    Hope this helps,
    Rock from VbCity

  • August 30, 2012 at 6:56 pm

    #1532171

    Thanks to everyone for the assistance

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

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