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.

score 17 · Accepted Answer · answered Nov 06 '09 at 12:45

17

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)

answered Nov 06 '09 at 12:45

avesse

347
2
3
8

While this is probably the best answer for a small case statement, is definitely not optimal for a switch statement casing hundreds of values. – J.S. Orris Jan 27 '23 at 20:33

Valentino Vranken · Answer 2 · 2014-08-25T10:17:21.407

5

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!

edited Aug 25 '14 at 10:17

answered Apr 18 '12 at 08:54

Valentino Vranken

5,597
1
26
28

4

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). – Aditya Feb 04 '14 at 07:25
5

@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! :) – Valentino Vranken Feb 04 '14 at 13:41

score 4 · Answer 3 · edited Jun 08 '17 at 07:28

4

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]

edited Jun 08 '17 at 07:28

Sam

1,264
14
19

answered Jun 26 '15 at 18:36

The Danish Dynamo

39
2

How to write SSIS switch/case expression?

3 Answers3

Linked