EvaluateAsExpression property of variables in SSIS

Once a variable is created in a package, it can be seen in the variables window but it's properties are not visible in the properties pane instantly. It's a kind of bug with BIDS, the package needs to be closed and re-opened again and only after that the property pane lists the variable. Select the variable in the property pane and of all the available properties, there would be two properties named "EvaluateAsExpression" and "Expression" property.

These properties are some on the most under-used or under-rated gems in SSIS as per my views. "EvaluateAsExpression" make possible what I call, variable inside a variable. In a real time implementation, I have seen that extensive script tasks were used in order to get the same output which can be achieved by the correct use of both of these properties.

In your top-level or the controller packages, variables should be created which reads value from the configuration files and, inside child packages, new values from these values can be deduced using these properties instead of using script tasks. Also this would centralize the logic and would leave less margin for errors and reduce maintenance overhead.

Eg: Consider two packages, one is a master package and one is a child package. I define three variables: ustrDB, ustrServer, ustrTable; in my master package. Consider the child package is configured with parent package configuration package setting. Say in child packages I am using two source connection adapters, one of OLE DB and other of ADO.Net type. To form a connection string, we just need two variables, form an expression concatenating the drivers and the three variables available from the parent package. Set this expression in the "Expression" property just as you would concatenate a string and before that set the "EvaluateAsExpression" property to true.

In case if these properties are not used, usage of Script Task becomes inevitable. I have worked with datawarehousing projects having more that 50-60 variables in the controller/parent package and more that 40 child packages using at least half of these variables. Imagine the duplication of code in all the packages, maintenance overhead and issues while debugging the same. To add to it, once you migrate this to 64-bit environment and the nuke is ON as Script task has many issues in 64-bit environment.

Use these properties once and you would be a fan of it as I am ! More documentation on this topic can be found on BOL.