none
SSIS 2008 versus 2005 explicite type cast problem in component derived columns ( error code 0xC0049064 )

    Question

  • Hi all,

    I noticed a very strange behaviour of my derived column component between a 2005 SSIS package and 2008 SSIS  package. When I copy the derived column component (once created and migrated to SSIS 2008) from one SSIS 2008 package to an new (control flow) package, I noticed it al works fine. But when I create a new derived column component (doing exactly the same thing, all data and settings are compared) it fails because in SSIS 2008 you need to perform a type cast because my input column is char and my ouput column is a numeric. In 2005 you could change this via the type combobox but in 2008 you need to cast it. So I did, but I could the default error message

    "[Derived Column [??]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (??)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "??" (??)" 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." 

    A] In SSIS 2005 (migrated to 2008) it's,

    Derived column Name Derived Column  Expression Data type  Precision  Scale
    -------------------------------------------------------------------------------------------------------------------  
    num_Currency_Rate <add as new column>  Currency_Rate numeric [DT_NUMERIC] 22  6


    B] New created in SSIS 2008 it will be,

    Derived column Name Derived Column  Expression   Data type  Precision  Scale
    -----------------------------------------------------------------------------------------------------------------------------  
    num_Currency_Rate <add as new column>  (DT_NUMERIC,22,6)Currency_Rate numeric [DT_NUMERIC] 22  6

    So the new created data flow task of SSIS 2008 fails [B} with the error mesasge above and the SSIS 2005 data flow task (migrated to 2008) is succesfull executed. So I would say it's MS SSIS 2008 bug!

    Or can anyone tell me why this works for the migrated 2005 data flow task (derived column) while it's not working with the new created data flow task (derived column)?

    I'm lost here?

    Thanks in advance,
    Eric

    Wednesday, June 16, 2010 11:14 AM

Answers

  • MS developers, when the upgraded 2005 SSIS to 2008, cracked down on data types in derived columns and the like.

    Try this as an experiment: Take a DT_STR column and perform some sort of string operation on it in a derived column, like a SUBSTRING or RIGHT (there is no LEFT function, go figure). What output data type do you get? That's right, you get DT_WSTR, different from your input. Go FIGURE.

    Here's a nickel's worth of free advice: You no longer have the ability in 2008 Derived Column Transform to dictate the output column data type. Instead, you must explicitly CAST it to your desired type using the available TYPE CASTing functions.

    This has already been submitted to Microsoft in feedback and their response is "It works as designed." My comment on that is (if anyone from Microsoft is ever listening), "Then re-think the design, because the design specs SUCK."

    Sorry for the rant, not your fault. But yeah, it's a pain.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, June 16, 2010 11:43 AM