Integration Services Variables
Variables store values that a SQL Server Integration Services package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.
You can use variables in Integration Services packages for the following purposes:
Updating properties of package elements at run time. For example, you can dynamically set the number of concurrent executables that a Foreach Loop container allows.
Including an in-memory lookup table. For example, a package can run an Execute SQL task that loads a variable with data values.
Loading variables with data values and then using them to specify a search condition in a WHERE clause. For example, the script in a Script task can update the value of a variable that is used by a Transact-SQL statement in an Execute SQL task.
Loading a variable with an integer and then using the value to control looping within a package control flow. For example, you can use a variable in the evaluation expression of a For Loop container to control iteration.
Populating parameter values for Transact-SQL statements at run time. For example, a package can run an Execute SQL task and then use variables to dynamically set the parameters in a Transact-SQL statement.
Building expressions that include variable values. For example, the Derived Column transformation can populate a column with the result obtained by multiplying a variable value by a column value.
Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.
All variables—system and user-defined—can be used in the parameter bindings that the Execute SQL task uses to map variables to parameters in SQL statements. For more information, see SSIS Execute SQL Task and Working with Parameters and Return Codes in the Execute SQL Task.
Note
The names of user-defined and system variables are case sensitive.
You can create user-defined variables for all Integration Services container types: packages, Foreach Loop containers, For Loop containers, Sequence containers, tasks, and event handlers. User-defined variables are members of the Variables collection of the container.
If you create the package using SSIS Designer, you can see the members of the Variables collections in the Variables folders on the Package Explorer tab of SSIS Designer. The folders list user-defined variables and system variables.
You can configure user-defined variables in the following ways:
Provide a name and description for the variable.
Specify a namespace for the variable.
Indicate whether the variable raises an event when its value changes.
Indicate whether the variable is read-only or read/write.
Use the evaluation result of an expression to set the variable value.
Create the variable in the scope of the package or a package object such as a task.
Specify the value and data type of the variable.
The only configurable option on system variables is specifying whether they raise an event when they change value.
A different set of system variables is available for different container types. For more information about the system variables used by packages and their elements, see System Variables.
For more information about real-life use scenarios for variables, see Using Variables in Packages.
Namespace
Integration Services provides two namespaces, User and System. By default, custom variables are in the User namespace, and system variables are in the System namespace. You can create additional namespaces for user-defined variables and change the name of the User namespace, but you cannot change the name of the System namespace, add variables to the System namespace, or assign system variables to a different namespace.
Scope
A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container.
If a package runs other packages by using the Execute Package task, the variables defined in the scope of the calling package or the Execute Package task can be made available to the called package by using the Parent Package Variable configuration type. For more information, see SSIS Package Configurations.
Value
The value of a user-defined variable can be a literal or an expression. A variable includes options for setting the variable value and the data type of the value. The two properties must be compatible: for example, the use of a string value together with an integer data type is not valid.
If the variable is configured to evaluate as an expression, you must provide an expression. At run time, the expression is evaluated, and the variable is set to the evaluation result. For example, if a variable uses the expression DATEPART("month", GETDATE()) the value of the variable is the number equivalent of the month for the current date. The expression must be a valid expression that uses the SSIS expression grammar syntax. When an expression is used with variables, the expression can use literals and the operators and functions that the expression grammar provides, but the expression cannot reference the columns from a data flow in the package. The maximum length of an expression is 4000 characters. For more information, see Integration Services Expression Reference.
Configuring Variables
You can set properties through SSIS Designer or programmatically.
For more information about the properties that you can set in SSIS Designer, see Variables Window.
Configure Variables Programmatically
To learn more about variable properties, and for more information about programmatically setting these properties, see Variable.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services