SSIS - Derived Columns - Case Statment - Charindex and Substring

Question

I've the fololwing transformation in T-SQL:

SELECT [FIELD_A] = case when CHARINDEX('-', FIELD_B)>0 then LEFT(FIELD_B,CHARINDEX('-', FIELD_B)-1)
    else FIELD_B end,

How can I put this into a expression in Derived Column object using SQL Server Integration Services 2014?

Regards!!!

score 2 · Accepted Answer · answered Mar 29 '17 at 18:25

Derived Column transformations use SSIS Expression syntax. The syntax for this would work something like this:

FINDSTRING(FIELD_B, "-", 1) > 0 ? LEFT(FIELD_B, FINDSTRING(FIELD_B, "-", 1)-1) : FIELD_B

I haven't tested it, but it should get you going down the right path. To walk through it:

FINDSTRING takes 3 values - what text you want to search in, what text you want to search for, and which occurrence you want to find.
The "If" logic in SSIS expressions is the ? and : symbols. The ? follows the "If" boolean expression, and the : separates the "If true" and "If False" expressions.
Literal strings in SSIS Expressions need double-quotes around them.

1 Answers1