SSIS IF Expression as a CASE Statement

SSIS expressions support using an IF kind of syntax as a means of conditional assignment.  The syntax is straight forward and documented on MSDN here:

boolean_expression?expression1:expression2

An example of using IIF in an SSIS expression would be like this:

1 == 1 ? “True” : “False”

This expression is evaluated as If 1 is equal to 1 then show “True” else show “False”.  Simple enough unless your evaluation for conditional is more complex and not just True or False.  Consider that you must make a conditional assignment in an expression of a numeric value that is between 1 and 10.  The variable assignment is made based on this logic:

If the value is between 1 and 3 the assignment should be “Small”

If the value is between 4 and 6 the assignment should be “Medium”

If the value is between 7 and 9 the assignment should be “Large”

If the value is 10 the assignment should be “Largest”

If the value falls outside the expected range the assignment should be “HUGE”

This type of logic is easy enough with a CASE statement, but unfortunately SSIS expressions don’t support CASE or have a similar function.  You could nest your IF, but that can become confusing and be quite ugly.  This statement can actually be written using the SSIS conditional syntax.  So consider an SSIS package with two variables, the first called Random holds an integer value that will be evaluated.  A variable called Case is a string and the value will be dynamically assigned based upon the value of the variable Random.  The SSIS expression to meet the above logic would be written as this in the expression of Case:

@[User::Random]  >= 1  && @[User::Random] <= 3 ? “Small”  :
@[User::Random]  > 3  && @[User::Random] <= 6 ? “Medium” :
@[User::Random]  > 6  && @[User::Random] <= 9 ? “Large” :
@[User::Random]  == 10 ? “Largest”   : “HUGE”

To illustrate how this works create a package with the variables Random and Case.  Drag and drop two script tasks on the control flow pane, one that will assign a random integer between 1 and 13 to the Random variable and display message box displaying the value and the second script task to display the value of the Case variable.

image Assign the Random variable as a ReadWrite Variable in the first Script task

image

The C# script body assigning a random value between 1 and 13 is:

Random rnd = new Random();
int value = rnd.Next(1, 13);
Dts.Variables[“Random”].Value = value;
MessageBox.Show(Dts.Variables[“Random”].Value.ToString());

Configure the second script task to show the value of the Case variable, which is dynamically set based on the Random value, in a message box.  Insure that the Case variable is configured as a Readonly variable in the script task:

image

The C# code to display the value is:

 

MessageBox.Show(Dts.Variables[“Case”].Value.ToString());

Executing the package will result in a message box showing the Random value being displayd in a message box followed by the Case value.

image

image

To insure that the Case assignment is working create another variable called loop that is an integer.  Drop a For Loop container onto the control flow pane and configure it as shown below:

image image

     Now executing the package will loop through both message boxes 10 times and display the conditional value of the Case variable based on the Random variable value.

This syntax can be used in any object that accepts expressions in SSIS including derived column transformations.

You can download the package used in this post from my site here.

 

SSISEndtoEnd

Leave a Reply


Hit Counter provided by Sign Holders