SSIS - witing a CASE statement in Expression

Problem:

I'm setting up a package to import an excel spreadsheet into a SQL database. There is a column in the spreadsheet where I would like to pick out keywords and then put them in a new column. In SQL it would be like a basic case statement

case when column_A like '%Norwich%' then 'Norwich'
    when column_A like '%Ipswich%' then 'Ipswich'
    when column_A like '%Cambridge%' then 'Cambridge'
    else 'NA'
end as NewColumn

I have tried the below but I'm guessing its not working properly because I have now wildcards

[Report Title] == "Norwich" ? "Norwich" : [Report Title] == "Ipswich" ? "Ipswich" : [Report Title] == "Cambridge" ? "Cambridge" : "NA"

Example:

Report Title                      NewColumn

Norwich is in Norfolk             Norwich
Cambridge is in Cambridgeshire    Cambridge
Suffolk is home to Ipswich        Ipswich

Solution:

You have to use FINDSTRING() function with nested conditional operators to achieve that:

FINDSTRING([Report Title],"Norwich",1) > 0 ? "Norwich" : (
FINDSTRING([Report Title],"Ipswich",1) > 0 ? "Ipswich" : (
FINDSTRING([Report Title],"Cambridge",1) > 0 ? "Cambridge" : "NA"))

References

• Nested Conditional Operators in an SSIS Derived Column
• FINDSTRING (SSIS Expression)

Original post: https://stackoverflow.com/questions/56496440/witing-a-case-statement-in-ssis/56498039#56498039