Like any programming languages, variables in SSIS packages are used to store temporary values to make the whole package more flexible. For example, we can loop a folder to get each file and save its name to a variable for further handling. In SSIS packages, variables are case sensitive and can be divided into system variables and user-defined variables.
System variables are defined by SQL Server Integration Services and are used to store run-time information of the package and its objects. Developers cannot add any new system variables and are not recommended to change any values of system variables. The following is the list of the most useful system variables.
|System Variable||Data Type||Description|
|CreationDate||DateTime||The date and time which the package was created.|
|CreatorName||String||The name of the developer who created the package.|
|MachineName||String||The server name on which the package is running.|
|PackageID||String||The unique GUID of the package|
|PackageName||String||The name of the package.|
|StartTime||DateTime||The start running time of the package.|
|UserName||String||The name of the qualified account who ran the package.|
|TaskID||String||The unique GUID of the task.|
|TaskName||String||The name of the task.|
To see the whole system variables and their descriptions, please check them here.
User-defined Variables are the variables which are defined by developers and can be created as many as you want. Please follow the steps to create some variables in a new package.
- Before creating variables, we'll create a new package by right clicking "SSIS Packages" and choose "New SSIS Package".
- Open the package design window by double clicking the package Variables.dtsx. If the variable window is not shown up, you can click SSIS -> Variables to open it.
- Click "Add Variable" icon to add a new variable.
- Rename the variable to V1, keep the scope and data type as defaults and change the its value to 1. Then create the second variable V2 and assign its value to 2.
- Create the third variable as follows.
- This time, instead of setting the value of the variable directly we set the expression of the variable as @[User::V1] + @[User::V2].
Then Rename the package name to Variables.dtsx.
If you want to delete a variable, you can select the variable and click "Delete Variable" icon.
After the setting, its data type and value are greyed out because its type and value depends on the result of the expression. For details of setting the expression, we'll explain it in the later section.
The scope of the variables defines a range of the variables which can be accessed. The default scope is the package name which means the variable can be accessed in the whole package, like global variables in other languages. If you want a variable can only be accessed in a specific object in the package, you can simply select the variable and click "Move Variable" icon to change it. In the next section, we'll change it to a script task level.
The data type of a variable can be set one of the following. The default type is Int32.
Boolean, Byte, Char, DateTime, DBNull, Decimal, Double, Int16, Int32, Object, SByte, Single, String, UInt32 and UInt64.
DBNull is the database Null value and Object is the most powerful data type in the package, it can stand for a part of records in a table, a picture or a list, etc.
If you want to see the systems variables or variables of all scopes, you can click the last icon in Variable window and the Variable Grid Options dialogue will be popped up as below.
Make "Show system variable" or "Show variables of all scopes" checked and click OK button to get the result you want.
If you click any variable in the Variables window, you will see its properties in the Properties window. If the Properties window is not shown up, you can click VIEW -> Properties Window or press F4 to open it. The following is the property of the variable V1.
The following is the properties which can be set in the Properties Window.
|Property Name||Description||Variable V1||Variable V2||Variable V3|
|Description||The description of the variable.|
|EvaluateAsExpression||If True, the property's value comes from the expression.||False||False||True|
|Expression||The expression that is assigned to the variable.||V1 + V2|
|Name||The variable name.||V1||V2||V3|
|Namespace||The namespace of the variable.||User||User||User|
|RaiseChangedEvent||If True, the OnVariableValueChanged event is raised when its value is changed.||False||False||False|
|ReadOnly||When False, the variable is read\write.||False||False||False|
|IncludeInDebugDump||Indicate if the variable value is included in the debug dump files.||True||True||False|
|Value||The value of the variable.||1||2||0|
|ValueType||The data type of the variable.||Int32||Int32||Int32|
Note: User and System are two namespaces provided by Integration Services. System namespace holds all the system variables and cannot be changed. User namespace holds the user-defined variables by default and you can create a new namespace and assign it to any custom variables if you want.