1

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.

user2407147
  • 1,508
  • 2
  • 22
  • 40

2 Answers2

3

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

Prabhat G
  • 2,974
  • 1
  • 22
  • 31
0

Sure. Within a data flow task (aka data pump) there is a Derived Column component where you can add columns to your data flow and include a custom expression.

Jim Horn
  • 879
  • 6
  • 14
  • Yes I've tried using the derived column within the data flow task using the substring and findstring expressions however, I can't seem to get it to do what I need it to do. Would you know how that expression would be wrote? – user2407147 Mar 18 '18 at 10:36