System Variables

System Variables

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

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.

System Variables for Packages

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

System variable	Data type	Description
CancelEvent	Int32	The handle to a Windows Event object that the task can signal to indicate that the task should stop running.
CreationDate	DateTime	The date that the package was created.
CreatorComputerName	String	The computer on which the package was created.
CreatorName	String	The name of the person who built the package.
ExecutionInstanceGUID	String	The unique identifier of the executing instance of a package.
InteractiveMode	Boolean	Indicates 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.
LocaleId	Int32	The locale that the package uses.
MachineName	String	The name of the computer on which the package is running.
OfflineMode	Boolean	Indicates whether the package is in offline mode. Offline mode does not acquire connections to data sources.
PackageID	String	The unique identifier of the package.
PackageName	String	The name of the package.
StartTime	DateTime	The time that the package started to run.
UserName	String	The account of the user who started the package. The user name is qualified by the domain name.
VersionBuild	Int32	The package version.
VersionComment	String	Comments about the package version.
VersionGUID	String	The unique identifier of the version.
VersionMajor	Int32	The major version of the package.
VersionMinor	Int32	The minor version of the package.

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 variable	Data type	Description	Container
LocaleId	Int32	The locale that the container uses.	For Loop container Foreach Loop container Sequence container

System variable

Data type

Description

Container

LocaleId

Int32

The locale that the container uses.

For Loop container

Foreach Loop container

Sequence container

System Variables for Tasks

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

System variable	Data type	Description
CreationName	String	The name of the task.
LocaleId	Int32	The locale that the task uses.
TaskID	String	The unique identifier of a task instance.
TaskName	String	The name of the task instance.
TaskTransactionOption	Int32	The transaction option that the task uses.

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.

Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Concepts

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

Help and Information

Getting SQL Server 2008 Assistance