What is the variable value at runtime?

SSIS #116–What is the variable value at runtime?

5 different ways to set values for variables

In general, we have the following 5 different ways to set values for variables. The question we need to ask is what is the order of precedence during runtime when we are using all these 5 different ways to set values for a variable.

To summarize, here are the values I set for the variable varCubeName using 5 different ways:
1. Cube Name in Value: in the Variable Editor in the Value field
2. Cube Name in Expression: in the Variable Editor in the Expression field
3. Cube Name in Package Configuration: in the package configuration file
4. Cube Name from Execute SQL Task: in the Execute SQL Task
5. Cube Name in Script Task: in a scrip task

Conclusion on the precedence order

1. Cube Name in Expression: the value is set in the Variable Editor in the Expression field. The value set in this way has the highest precedence order and overwrites the values in all other ways.
2. Cube Name from Execute SQL Task: the value is set in the Execute SQL Task. It has thesecond highest precedence order. Cube Name in Script Task has the same precedence order.
3. Cube Name in Package Configuration: the value is set in the package configuration file. It can only overwrites the variable’s default value.
4. Cube Name in Value: the value is set in the Variable Editor in the Value field. This value is usually called the default value, because it can be overwritten by all the proceeding ways during runtime.

Lessons learned

During the SSIS package deployment, your DBA decided to include all the variables along with all the connection strings as the property/value pairs in the package configuration files, and somehow your packages are not working the way you had expected.

During development, your packages are not working the way you would expect and you decided to debug your variables and found out that the variable value at runtime was incorrect.

The above two scenarios happened to me in the past and they have inspired this blog.

The lessons I’ve learned are:

• During deployment, do not include variables property/value pair in the package configuration file. Variables should only be handled in the SSIS packages.
• If variable expression is sufficient, use variable expression only.
• If you need to combine different ways to set variable values at runtime, only use these two combinations.

Default value +  Execute SQL Task, or

Default value +  Script Task


Note:http://bisherryli.com/category/ssis-best-practices/



SSIS #114 – How many ways can you do INSERT-UPDATE?

I’ve always wanted to do a benchmark comparison so I can proudly say that my “normal” way is the best in terms of run time.

1. STAGE-TSQL: Use the data flow to bring the raw data into staging, and use do the INSERT-UPDATE in TSQL. This is my “normal” way. And the best way according to my testing. The name STAGE-TSQL implies: 1) two steps are involved, 2) raw data is staged first, 3) INSERT-UPDATE are done in TSQL only.
2. UPDATE-ALL: Only one step is involved. INSERT-UPDATE is done in one data flow step. 1) Transformation OLE DB Command is used for UPDATE, 2) Destination OLE DB Destination is used for INSERT.
3. UPDATE-STAGE: Two steps are involved. 1) INSERT is done in the data flow step, 2) but the matching rows are saved to a staging table, and UPDATE is done in TSQL using the matching rows.
4. UPDATE-DELTA: similar to number 2 UPDATE-ALL. In stead of directly sending all matching rows to transformation OLE DB Command for UPDATE, the Script Component transformation is used to determine if there are actually changed rows. Send data to Transformation OLE DB Command only if there are changes in the matching rows.

1. 5. STAGE-TSQL is the winner!

   It’s a relief knowing that our skills in TSQL are serving us well.

SSIS #111 – Looking for Conditional IIF() in SSIS Expression?




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).

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