Sql Script To Ssis Expression

I have the below T-SQL line of query that I'm trying to translate into Visual studio SSIS expression into derived column task. So tableA has just [Work item /Submission no#] colum

Solution 1:

Solution

I will suggested that you first add a derived column (you can name it IsNumeric), with the following expression:

(DT_NUMERIC, 18, 2)SUBSTRING([Work item /Submission no#], 4, 2) == (DT_NUMERIC, 18, 2)SUBSTRING([Work item /Submission no#], 4, 2) ? 1 : 0

Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to Ignore failure on Error

Add another Derived Column connect to the first on with the following expression

REPLACENULL([IsNumeric],0) == 1 ? LEFT([Work item /Submission no#], 15) : LEFT([Work item /Submission no#], 16)

Because the first one may throws an error

For detailed informations just follow this article:

• http://bidn.com/blogs/DustinRyan/ssis/915/check-isnumeric-with-derived-column-transform-in-ssis-package

Update 1 - Screenshots

Based on your comments, i will provide some screenshots

Data Flow overview

First Derived Column

First Derived Column Error Output Configuration

Second Derived Column

Solution 2:

I believe something like this should work for you:

(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)

Tried to format for this site but you might have to delete some of the returns. This should cast your column to numeric and compare it to itself (thus if the conversion fails it will be NULL and not equal).

Edit: Here is the string un-formatted:

(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)

You may also need to match the precision and scale specifications for DT_NUMERIC to match your own requirements.

Solution 3:

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

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

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