How to Make an If Statement in Ssis

Case or if statement in SSIS expression

Evaluate variable as Expression

The easiest way is to Add a Variable @[User::ConnectionSting] of type String, Select to Evaluate this variable as expression, and use the following expression:

@[$Project::Parameter] == "SERVER1" ? @[$Project::SERVER1_ConnectionString]  
: ( @[$Project::Parameter] == "SERVER2" ? @[$Project::SERVER2_ConnectionString] 
: ( @[$Project::Parameter] == "SERVER3" ? @[$Project::SERVER3_ConnectionString]  
: ""))

Then Click on the OLEDB Connection Manager, press F4 to Show the properties Tab, GoTo Expression, Select the ConnectionString property and use the following expression:

@[User::ConnectionSting]

And Click on the data flow task and the tasks that uses the connection and Set the Delay Validation property to True

Using Expression Task

You can use the same method but instead of evaluating @[User::ConnectionSting] as expression, add an Expression Task at the package beginning and use the following expression:

@[User::ConnectionSting] = (@[$Project::Parameter] == "SERVER1" ? 
@[$Project::SERVER1_ConnectionString] : ( @[$Project::Parameter] == "SERVER2" ? @[$Project::SERVER2_ConnectionString] : ( @[$Project::Parameter] == "SERVER3" ? @[$Project::SERVER3_ConnectionString] : "")))

SSIS-convert IF and ELSE statement or Case When statement in SSIS derived column expression

These are the basics of SSIS...

In short: use Conditional from Operaters in Derived Column Transformation Editor:

«boolean_expression» ? «when_true» : «when_false»

For CASE implementation you have to use "multi level" Conditional...

«boolean_expression» ? «when_true» : («boolean_expression» ? «when_true» : «when_false»)

SSIS Derived Column (if then...else)

The SSIS expression language supports the ternary operator ? :

(LEN([column1]) > 8) ? column1 : replicate("0", (18 - LEN([column1]))) + [column1]

That expression ought to work but it doesn't because the REPLICATE call is going to provide metadata back stating it's 4k nvarchar characters (the limit). If you're deadset on getting it to work that way, comment and I'll hack the expression to size the output of replicate before concatenating with column1

An easier approach is to just always add 8 leading zeros to the expression and then slice it off from the right.

RIGHT(REPLICATE("0",8) + column1,8)

You might need 18 on the second as your example seemed to use 18 but the concept will be the same.

SSIS: IF else in Derived Column expressions

You are ending your expression with an incomplete ternary expression. I am adding some line breaks and indentation to make it more readable:

(LEN([TIME OCC]) == 4) ? 
(SUBSTRING([TIME OCC],1,2) + ":" +  SUBSTRING([TIME OCC],3,2)) : 
  (LEN([TIME OCC]) == 3) ? 
  (SUBSTRING([TIME OCC],1,1) + ":" + SUBSTRING([TIME OCC],2,2)) : 
    (LEN([TIME OCC]) == 2) ? 
    (SUBSTRING([TIME OCC],1,2) + ":00") : 
      (LEN([TIME OCC]) == 1) ? 
      (SUBSTRING([TIME OCC],1,1) + ":00")  <-- there needs to be a : with an ELSE condition here

I don't know if it's necessary, but I would also put a set of parenthesis around each of the nested ternary expressions.

---

Related Topics