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

Hadi · Accepted Answer · 2017-12-21 19:22:43Z

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

edited Dec 21, 2017 at 19:22

answered Dec 21, 2017 at 18:27

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

katy89 Over a year ago

I just saw this and sorry for not asking on this earlier. So I need to add two columns in the derived column 1) IsNumeric with above formula, 2)SubmissionCommon with above exp (replacing IsNumeric - expression), but it gives me an error for the second one

Hadi Over a year ago

No, add 2 derived column transformations, every column in one component: (2 derived columns transformation -> on column in each transformation): first Isnumeric then SubmissionControl

Hadi Over a year ago

If you are confused i can add some screenshots, give me a reply if you are interested

katy89 Over a year ago

ya .. I would be more grateful. Thanks so much.

katy89 Over a year ago

I tried the same and It worked like a charm. Thanks a ton! and thanks much for your patience :)

Jacob H · Accepted Answer · 2017-12-14 18:02:44Z

1

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.

edited Dec 14, 2017 at 18:02

answered Dec 14, 2017 at 17:57

Jacob H

2,5341 gold badge14 silver badges31 bronze badges

2 Comments

katy89 Over a year ago

Apologize if I am asking basic question. I'n new to SSIS and getting to know stuff. Should I use the above expression to both the Derived columns ? like ColA = "above expression" and next row ColB=" above Expression"

Jacob H Over a year ago

Doesn't look like there formula in the second column in your initial post. You should be able to simply use [Work item /Submission no#] as SubmissionNumber.

KeithL · Accepted Answer · 2017-12-14 20:08:30Z

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

answered Dec 14, 2017 at 20:08

KeithL

5,6193 gold badges23 silver badges30 bronze badges

1 Comment

Hadi Over a year ago

even if i posted a different method. I prefer to do this using a script component because it is more powerful +1

Collectives™ on Stack Overflow

SQL script to SSIS expression

3 Answers 3

Solution

Update 1 - Screenshots

5 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Solution

Update 1 - Screenshots

5 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related