I have a SSIS package, where I have four different data flow task. Each data flow task (say, A, B, C, D) has same derived column expression and appends the results from different oledb source to the same oledb destination.
I get an error as below for data flow task C alone, and works with no issues for A, B, and D though all has same derived column expression.
Derived Column expression:
(DT_NUMERIC,18,2)SUBSTRING([Work item /Submission no#],4,2) == (DT_NUMERIC,18,2)SUBSTRING([Work item /Submission no#],4,2) ? LEFT([Work item /Submission no#],15) : LEFT([Work item /Submission no#],16)
SSIS error showing on Data flow task of C :
[Derived Column [100]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[SubmissionCommon]" 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.
First of all, i assumes that you are using (DT_NUMERIC,18,2)SUBSTRING([Work item /Submission no#],4,2) == (DT_NUMERIC,18,2)SUBSTRING([Work item /Submission no#],4,2) to check if SUBSTRING([Work item /Submission no#],4,2) is numeric or not.
I think that the expression you are using can throws error due to the cast operations used. because if SUBSTRING([Work item /Submission no#],4,2) is not numeric it will throw an error.
Just follow my answer on this question for a workaround: SQL script to SSIS expression