SSIS Conditional Column Similar to Power Query

Question

im a little new to SSIS coming from using Power Query in Excel a lot. Is it possible to create a conditional column in SSIS, similar to how you can in Power Query.

For example in Power Query you can create a conditional column to say IF column [FileName] contains USA, Canada, United States, America = "North America" else "null". It would create a new column with North America and anything that doesn't meet the criteria, it would be a null. Is something like this possible in SSIS. I have tried used the Substring and Findstring however it doesnt do exactly what I need.

Answer 1

Basically you're looking for a ternary operator condition like this in derived column:

(FINDSTRING([Name], "USA", 1) > 0) || (FINDSTRING([Name], "Canada", 1) > 0) || (FINDSTRING([Name], "United States", 1) > 0) || (FINDSTRING([Name], "America", 1) > 0) ? "North America" :  NULL(DT_WSTR, 13)

To explain it, I am indenting it as:

(FINDSTRING([Name], "USA", 1) > 0) || 
(FINDSTRING([Name], "Canada", 1) > 0) || 
(FINDSTRING([Name], "United States", 1) > 0) || 
(FINDSTRING([Name], "America", 1) > 0) ? "North America" : 
                                          NULL(DT_WSTR, 13)

To modify it further, you can use the following guide:

• For Contains like '%value%' : FINDSTRING(col, "value", 1) > 0
• Start with like 'value%' : FINDSTRING(col, "value", 1) == 1
• End with like '%value' : REVERSE(LEFT(REVERSE(col), X)) == "value"

source: sqlservercentral blog