How to write SSIS switch/case expression?

Question

This is a SQL Server Integration Services (SSIS) expressions question (I'm pretty new to it).

I would like to write a switch/case expression in a Derived Column transform - basically the new column can have 5 different possible values, based on the value of an input column. All I got from Google is the (condition) ? (true value) : (false value) technique, but this only provides for two possible values. Does SSIS expressions have a switch/case type expression?

I thought of using sequential Derived Column transforms, or creating a temporary lookup table, but that just seems more complicated that it really should be.

Thanks.

Solution 1

I have used the equivalent of a if-elseif-else statement to solve the problem, like this:

(condition1) ? (true value1) : (condition2) ? (true value2) : (false value)

Solution 2

Even though, technically, the answer by avesse will work, writing/maintaining the expression is not the most interesting task.

To avoid writing complex expressions in the Derived Column transformation, I'd recommend to use a Script transformation. Doing that allows you to use .NET code to write a switch statement, much nicer to write and maintain. Your colleagues will appreciate it!

Solution 3

Simply structure your ?: statements as stacked

@[User::SomeVariable] == 2 ? "SomeVariable is 2"
:
@[User::SomeVariable] == 3 ? "SomeVariable is 3"
:
@[User::SomeVariable] == 4 ? "SomeVariable is 4" 
:
"SomeVariable is not 2,3,4 is actually" + @[User::SomeVariable] 

Comments

[Aditya]

I don't think so. You can write & maintain the expressions as complex as you want, condition, you should understand it. What if the ETL developer is a newbie to C# ? I would avoid script transformations as much as possible. (Just my view).

[Valentino Vranken]

@Aditya To be honest I wouldn't trust an ETL developer who doesn't understand basic logic expressions in C#/VB.NET. Imagine that same developer has to add a sixth condition to the .. ? .. : .. ? .. : .. ? .. : (and so on) expression. Chances are high this results in a bug. But everyone is entitled to their own opinion of course! :)