System Variables

 

SQL Server Integration Services 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.

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

System variableData typeDescription
CancelEventInt32The handle to a Windows Event object that the task can signal to indicate that the task should stop running.
ContainerStartTimeDateTimeThe start time of the container.
CreationDateDateTimeThe date that the package was created.
CreatorComputerNameStringThe computer on which the package was created.
CreatorNameStringThe name of the person who built the package.
ExecutionInstanceGUIDStringThe unique identifier of the executing instance of a package.
FailedConfigurationsStringThe names of package configurations that have failed.
IgnoreConfigurationsOnLoadBooleanIndicates whether package configurations are ignored when loading the package.
InteractiveModeBooleanIndicates whether the package is run in interactive mode. If a package is running in SSIS Designer, this property is set to True. If a package is running using the DTExec command prompt utility, the property is set to False.
LocaleIdInt32The locale that the package uses.
MachineNameStringThe name of the computer on which the package is running.
OfflineModeBooleanIndicates whether the package is in offline mode. Offline mode does not acquire connections to data sources.
PackageIDStringThe unique identifier of the package.
PackageNameStringThe name of the package.
StartTimeDateTimeThe time that the package started to run.
ServerExecutionIDInt64Execution ID for the package that is executed on the Integration Services server.

The default value is zero. The value is changed only if the package is executed by ISServerExec on the Integration Services Server. When there is a child package, the value is passed from the parent package to child package.
UserNameStringThe account of the user who started the package. The user name is qualified by the domain name.
VersionBuildInt32The package version.
VersionCommentStringComments about the package version.
VersionGUIDStringThe unique identifier of the version.
VersionMajorInt32The major version of the package.
VersionMinorInt32The minor version of the package.

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

System variableData typeDescriptionContainer
LocaleIdInt32The locale that the container uses.For Loop container

Foreach Loop container

Sequence container

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

System variableData typeDescription
CreationNameStringThe name of the task.
LocaleIdInt32The locale that the task uses.
TaskIDStringThe unique identifier of a task instance.
TaskNameStringThe name of the task instance.
TaskTransactionOptionInt32The transaction option that the task uses.

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

System variableData typeDescriptionEvent handler
CancelBooleanIndicates whether the event handler stops running when an error, warning, or query cancellation occurs.OnError event handler

OnWarning event handler

OnQueryCancel event handler
ErrorCodeInt32The error identifier.OnError event handler

OnInformation event handler

OnWarning event handler
ErrorDescriptionStringThe description of the error.OnError event handler

OnInformation event handler

OnWarning event handler
ExecutionStatusBooleanThe current execution status.OnExecStatusChanged event handler
ExecutionValueDBNullThe execution value.OnTaskFailed event handler
LocaleIdInt32The locale that the event handler uses.All event handlers
PercentCompleteInt32The percentage of completed work.OnProgress event handler
ProgressCountHighInt32The high part of a 64-bit value that indicates the total number of operations processed by the OnProgress event.OnProgress event handler
ProgressCountLowInt32The low part of a 64-bit value that indicates the total number of operations processed by the OnProgress event.OnProgress event handler
ProgressDescriptionStringDescription of the progress.OnProgress event handler
PropagateBooleanIndicates 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.
All event handlers
SourceDescriptionStringThe description of the executable in the event handler that raised the event.All event handlers
SourceIDStringThe unique identifier of the executable in the event handler that raised the event.All event handlers
SourceNameStringThe name of the executable in the event handler that raised the event.All event handlers
VariableDescriptionStringThe variable description.OnVariableValueChanged event handler
VariableIDStringThe unique identifier of the variable.OnVariableValueChanged event handler

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.

Map Query Parameters to Variables in an Execute SQL Task

Show: