Clever Geek Handbook

SSIS - Derived Columns - Data Register - Charindex and Substring

I have a fololwing to T-SQL conversion:

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 expression into a Derived Column using SQL Server 2014 Integration Services?

Regards!!!

+3


source to share


1 answer


Column derived transforms 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 on the right track. Go through it:



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

Search reference: https://docs.microsoft.com/en-us/sql/integration-services/expressions/findstring-ssis-expression

Conditional link: https://docs.microsoft.com/en-us/sql/integration-services/expressions/conditional-ssis-expression

+2


source






More articles:


All Articles