There is no IIF statement in SSIS scripting functions
If you are looking for IIF() for SSIS expressions, you’ll be disappointed.
Most of us are familiar with the IIF statement in SSRS expressions (or in many other scripting languages).
But in SSIS, any Program Flow functions are unmistakably missing. Here are all the function categories you can see in the Expression Builder in SSIS. Program Flow is not one of them.
We can achieve IIF() using ( Condition? Value_when_true : Value_when_false)
There is a very good explanation for the missing IIF statement in SSIS. Before I go too far on this topic, I want to give you the good news first. The Integration Services did give us a tool to accomplish the same function as the IIF statement does, only in different disguise.
Here is what we can use:
( Condition? Value_when_true : Value_when_false)
You can write any acceptable expression in the Condition part, but it’ll only make sense for the expression to include at least one variable in order to achieve the goal of dynamic as you set out to achieve with IIF in the first place.
The value_when_true and value_when_false part can obviously include variables too.
Here is an example. For a user variable varSourceServerPrefix, I want to set it to an alia name of the linked server in our development environment, but set it to blank in the production server.
( @[User::varProduction] == 0? @[User::varLinkedServer] : "")
Processing flows are largely controlled by the Precedence Constraints
There is an even better reason why Integration Services are missing the entire category of Program Flow Functions.
In Integration Services, processing flows are largely controlled by the Precedence Constraints between tasks. These Precedence Constraints are almost completely controlled by conditions that are expressed using variables, while variables in Integration Services can be manipulated by Script Tasks.
For examples how variables can be manipulated by Script Tasks and example of Precedence Constraints:
Note: the parentheses are not really required. I don’t think the parentheses are required even in nested conditional expressions. It certainly doesn’t hurt to have the parentheses in nested conditional expressions for better readability.
Here is the MSDN link on this: http://msdn.microsoft.com/en-us/library/ms141680.aspx