System Variables

SQL Server 2005 Integration Services (SSIS) provides a set of system variables that store information about the running package and its objects. These variables can be used in expressions and property expressions to customize packages, containers, tasks, and event handlers.

All variables—system and user-defined— can be used in the parameter bindings that the Execute SQL task uses to map variables to parameters.

System Variables for Packages

The following table describes the system variables that Integration Services provides for packages.

System Variables for Containers

The following table describes the system variables that Integration Services provides for the For Loop, Foreach Loop, and Sequence containers.

System Variables for Tasks

The following table describes the system variables that Integration Services provides for tasks.

System Variables for Event Handlers

The following table describes the system variables that Integration Services provides for event handlers. Not all variables are available to all event handlers.

System variable

Data type

Description

Event handler

Cancel

Boolean

Indicates whether the event handler stops running when an error, warning, or query cancellation occurs.

OnError event handler

OnWarning event handler

OnQueryCancel event handler

ErrorCode

Int32

The error identifier.

OnError event handler

OnInformation event handler

OnWarning event handler

ErrorDescription

String

The description of the error.

OnError event handler

OnInformation event handler

OnWarning event handler

ExecutionStatus

Boolean

The current execution status.

OnExecStatusChanged event handler

ExecutionValue

DBNull

The execution value.

OnTaskFailed event handler

LocaleId

Int32

The locale that the event handler uses.

All event handlers

PercentComplete

Int32

The percentage of completed work.

OnProgress event handler

ProgressCountHigh

Int32

The high part of a 64-bit value that indicates the total number of operations processed by the OnProgress event.

OnProgress event handler

ProgressCountLow

Int32

The low part of a 64-bit value that indicates the total number of operations processed by the OnProgress event.

OnProgress event handler

ProgressDescription

String

Description of the progress.

OnProgress event handler

Propagate

Boolean

Indicates whether the event is propagated to a higher level event handler.

Note:

The value of the Propagate variable is disregarded during the validation of the package. If you set Propagate to False in a child package, this does not prevent an event from propagating up to the parent package. To prevent the parent package from handling the event, set the DisableEventHandlers property of the Execute Package task to True.

All event handlers

SourceDescription

String

The description of the executable in the event handler that raised the event.

All event handlers

SourceID

String

The unique identifier of the executable in the event handler that raised the event.

All event handlers

SourceName

String

The name of the executable in the event handler that raised the event.

All event handlers

VariableDescription

String

The variable description.

OnVariableValueChanged event handler

VariableID

String

The unique identifier of the variable.

OnVariableValueChanged event handler

Using System Variables in Parameter Bindings

It is frequently useful to save the values of system variables in tables when the package is run. For example, a package that dynamically creates a table and writes the GUID of the package execution instance that created the table in a table column.

If you use system variables to map to parameters in the SQL statement that an Execute SQL task uses, it is important that you set the data type of each parameter binding to the data type of the system variable. Otherwise, the values of system variables may be translated incorrectly. For example, if the ExecutionInstanceGUID system variable, which has the string data type and contains a string that represents the GUID of the executing instance of a package, is used in a parameter binding with the GUID data type, the GUID of the package instance will be translated incorrectly.

This rule applies to user-defined variables as well. But, whereas the data types of system variables cannot be changed and you have to tailor your use of these variables to fit the data types, user-defined are more flexible. The user-defined variables that are used in parameter bindings are usually defined with data types that are compatible with the data types of parameters to which they are mapped.

See Also

Concepts

Integration Services Packages
Integration Services Containers
Integration Services Tasks
Integration Services Event Handlers
Integration Services Variables

Help and Information

Getting SQL Server 2005 Assistance

Change History