CODEWITHSUNDEEP
sql-serverparametersssisexpressionssis-2012
jdids
jdids

Reputation: 581

Case or if statement in SSIS expression

I have a project parameter in SSIS that I want to use to be able to change our data source connections.

I'm trying to write an expression in the connection properties but I'm coming up short. I want to be able to evaluate a few different values and return different values if true

Here's what I have

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

I've tried adding in other servers to this code and I must not be understanding how you can evaluate additional results and set additional values

Basically, I'm looking to do this

If Parameter = SERVER1 then give me SERVER1_ConnectionString, if Parameter = SERVER2, then give me SERVER2_ConnectionString and so on...

EDIT: The syntax was correct but my issue was having a typo my project parameter

Upvotes: 1

Views: 4546

Answers (1)

Hadi
Hadi

Reputation: 37348

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] : "")))

Upvotes: 1

Related Questions