Variable Tips and Tricks

Variables can be used in a variety of ways to simplify your packages. The following section describes some of the more common things people do with variables.

EvaluateAsExpression in SQL Task with Parameters

You might want to use the EvaluateAsExpression feature of variables to simplify the SQL script in the Execute SQL Task. The feature is particularly useful for SQL queries that take parameters.

The SQL Task provides a way to substitute variables as parameters in queries. It's called parameter mapping; however, that feature is limited to only certain parts of the query. For example, some OLEDB providers do not allow parameters to be used for column names. The ExpressionsInSQLTask sample shows how you can use the EvaluateAsExpression property on variables to work around this limitation.

The ExpressionsInSQLTask package has three tasks. The first task is a SQL Task and it creates a table in the ISSAMPLES database and populates it with data. The second task is also a SQL Task. It uses a query retrieved from a variable to determine the total number of a particular type of cookie sold. The variable uses the EvaluateAsExpression property to build the query. During execution, the expression returns which column to query based on the CookieType variable. The third task is a Script Task that merely pops up a message box with the total. This is a simple example of how you can use the EvaluateAsExpression property of variables and expressions to prebuild or otherwise preprocess variable values before returning the value to the caller. To see the value change, you can change the CookieType value to one of the following cookie names: DoubleDutch, LemonCoolers, AllAbouts, ThinMints, Samoas, Tagalongs, DoSiDos, and Trefoils.

If you study this package, it should give you some ideas how to use variables and the EvaluateAsExpression property to simplify your packages or to work around infrastructure limitations.

Hiding Parent Variables in Subpackages

Child packages often require the values of variables found in their parent package and typically access those values by directly referencing variables found in the parent package. This can be problematic when designing or executing the child package standalone because parent package variables are only available to the child package when the child package is running as a child. To resolve these issues, it's possible to create variables in the child package with the same names as the parent package variables. Then, when you're done designing and debugging the child package, you can remove the temporary variables. When the package is executed as a child package, it will reference the parent variables.

Storing Objects in Variables

Variables hold things like filenames, numbers, table names, and other integral and string value types. However, variables can also contain objects, or more accurately, they can contain pointers. This means that you can create an object such as a collection of names, a .NET object or a COM object and store its memory location in the variable. Other Integration Services components can then access that object by getting the variable value and using it to access the object. This is especially useful for building collections that will later be consumed by a Foreach Enumerator. For example, you can build a collection of database names in the SQL Task. Later, you can iterate over each of them in the ForEach Loop with workflow to back up each of the databases. See Chapter 13, "Looping and Foreach Enumerators," for more information about this.