SSIS Variable

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 VariableData TypeDescription
CreationDateDateTimeThe date and time which the package was created.
CreatorNameStringThe name of the developer who created the package.
MachineNameStringThe server name on which the package is running.
PackageIDStringThe unique GUID of the package
PackageNameStringThe name of the package.
StartTimeDateTimeThe start running time of the package.
UserNameStringThe name of the qualified account who ran the package.
TaskIDStringThe unique GUID of the task.
TaskNameStringThe 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.

  1. Before creating variables, we'll create a new package by right clicking "SSIS Packages" and choose "New SSIS Package".
  2. New SSIS Package

    Then Rename the package name to Variables.dtsx.


  3. 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.
  4. Open SSIS Variables Window


  5. Click "Add Variable" icon to add a new variable.
  6. Add an SSIS Variable


  7. 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.
  8. Create or delete SSIS variables

    If you want to delete a variable, you can select the variable and click "Delete Variable" icon.


  9. Create the third variable as follows.
  10. Create Third SSIS Variable With Expression


  11. This time, instead of setting the value of the variable directly we set the expression of the variable as @[User::V1] + @[User::V2].
  12. 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.

    SSIS Variable Grid Options

    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.

SSIS Variable Properties

The following is the properties which can be set in the Properties Window.

Property NameDescriptionVariable V1Variable V2Variable V3
DescriptionThe description of the variable.   
EvaluateAsExpressionIf True, the property's value comes from the expression.FalseFalseTrue
ExpressionThe expression that is assigned to the variable.  V1 + V2
NameThe variable name.V1V2V3
NamespaceThe namespace of the variable.UserUserUser
RaiseChangedEventIf True, the OnVariableValueChanged event is raised when its value is changed.FalseFalseFalse
ReadOnlyWhen False, the variable is read\write.FalseFalseFalse
IncludeInDebugDumpIndicate if the variable value is included in the debug dump files.TrueTrueFalse
ValueThe value of the variable.120
ValueTypeThe data type of the variable.Int32Int32Int32

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.