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.
Integration Services supports two types of variables.
User-defined variables are defined by package developers, and system variables are defined by Integration Services.
You can configure user-defined variables in the following ways.
You can configure user-defined variables by setting the following properties in either the Variables window or the Properties window. Certain properties are available only in the Properties window.
| Option | Description |
|---|---|
| Description | Specifies the description of the variable. |
| EvaluateAsExpression | When the property is set to True, the expression provided is used to set the variable value. |
| Expression | Specifies the expression that is assigned to the variable. |
| Name | Specifies the variable name. |
| 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. |
| RaiseChangedEvent | When the property is set to True, the OnVariableValueChanged event is raised when the value of the variable changes. |
| ReadOnly | When the property is set to False, the variable is read\write. |
You can use the Variables window to create and modify user-defined variables and view system variables.
| Option | Description |
|---|---|
| Add Variable | Add a user-defined variable. |
| Move Variable | Click a variable in the list. Then click Move Variable to change the variable scope. In the Select New Scope dialog box, select the package or a container, task, or event handler in the package to change the variable scope. |
| Delete Variable | Select a variable from the list, and then click Delete Variable. |
| Grid Options | Click to open the Variable Grid Options dialog box where you can change the column selection and apply filters to the Variables window. For more information, see Variable Grid Options. |
| Name | View the variable name. You can update the name for user-defined variables. |
| Scope | View the scope of the variable. A variable has either the scope of the entire package or the scope of a container or task. The scope of the variable must be sufficient so that the variable is visible to any other tasks or components that need to read or set its value. You can change the scope by clicking the variable and then clicking Move Variable in the Variables window. |
| Data Type | View the data type of the variable. You can select a data type from the list for user-defined variables. |
| Value | View the variable value. You can update the value for user-defined variables. This value can be a literal or an expression, and the value can be a multi-line string. To assign an expression to the variable, click the ellipse button next to the Expression column in the Variables window. |
| Namespace | View the namespace name. User-defined variables are initially created in the User namespace, but you can change the namespace name in the Namespace field. To display this column, click Grid Options. |
| Raise Change Event | Indicate whether to raise the OnVariableValueChanged event when a value changes. You can update the value for user-defined and system variables. By default, the Variables window does not list this column. To display this column, click Grid Options. |
| Description | View the variable description. You can change the description for user-defined variables. By default, the Variables window does not list this column. To display this column, click Grid Options. |
| Expression | View the expression assigned to the variable. To assign an expression, click the ellipse button. |
If you assign an expression to a variable, a special icon marker displays next to the variable. This special icon marker also displays next to connection managers and tasks that have expressions set on them.