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#] 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

Answer1:

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 <strong>Ignore failure on Error</strong>

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:

    <li>http://bidn.com/blogs/DustinRyan/ssis/915/check-isnumeric-with-derived-column-transform-in-ssis-package</li> </ul>

    Update 1 - Screenshots

    Based on your comments, i will provide some screenshots

    <strong>Data Flow overview</strong>

    <img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/IQYDq.png" alt="enter image description here">

    <strong>First Derived Column</strong>

    <img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/kqZPY.png" alt="enter image description here">

    <strong>First Derived Column Error Output Configuration</strong>

    <img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/Yx52o.png" alt="enter image description here">

    <strong>Second Derived Column</strong>

    <img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/LahQz.png" alt="enter image description here">

    Answer2:

    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.

    Answer3:

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

    人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:SQL script to SSIS expression