SQL script to SSIS expression

Question

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#] column, but I need to split them into two column like SubmissionCommon and SubmissionNumber in TableB when below case is executed.

CASE 
    WHEN ISNUMERIC(SUBSTRING([Work item /Submission no#], 4, 2)) = 1
       THEN LEFT([Work item /Submission no#], 15)
       ELSE LEFT([Work item /Submission no#], 16)
END AS SubmissionCommon,
[Work item /Submission no#] AS SubmissionNumber

Answer 1

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

Answer 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.

Answer 3

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