SSIS Derived Column error on expression

1
votes

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.

2
sql-serverssisetlsql-server-data-toolsderived-column
I think that the error cause is that SUBSTRING([Work item /Submission no#],4,2) cannot be parsed to (DT_NUMERIC,18,2) in some rows – Hadi
Does the error take place if you run data flow task C while A, B, and D are disabled? Also, have you fully reviewed all of the Work item and Submission No# values from the oledb source used by C? A NULL value could cause a problem in the division used in the expression. Running a SQL query with an ISNUMERIC function against the Work item and Submission No# fields in this oledb source may provide the answer. – user3662215
No, I am running A, B , C and D all in different data flow task but within a same SSIS package.It throws an error at C, around 27494 row out of 30000 rows. – katy89
i feel like i already answered this question earlier this week – KeithL
@KeithL welcome to the club... – Jacob H

2 Answers

1
votes

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

1
votes

i did answer this already:

if you prefer c# script component (make sure to add input and output columns):

string test = Row.YourRowToTest;
int someNum; //catches output

Row.ColumnName = int.TryParse(test.Substring(4,2),out someNum) == false
                 ?test.Substring(1, 16)
                 :test.Substring(1, 15);