Updated March 15, 2023
What are SSIS Variables?
- A variable is a named object that stores single or multiple values that several SSIS modules can reference throughout the package’s execution.
- SSIS variables are introduced to store values which a SQL Server Integration Services (SSIS) package and its tasks, event handlers, and containers can implement at run time. For example, the scripts available in the Script task and the Script module can apply variables. Further, the priority constraints that order jobs and containers hooked on to a workflow can implement variables after their constraint definitions consist of expressions. An expression is a piece of code that returns a value.
How to set SSIS variables?
Let us discuss how to set SSIS variables and their properties in the variable window:
- In the SSDT (SQL Server Data Tools), visit the Integration Service project, which contains the package that the user needs.
- It would help if you right-clicked the package in Solution Explorer to open it.
- Click on the Variables on the SSIS menu bar, whereby by mapping the View, one can show the Variables window optionally. On the keyboard page available in the Options dialog box, variables command here to a key grouping of your selecting.
- You can optionally click on the menu Grid Options in the Variables window. After that, choose the columns for performing in the Variables window and then choose the filters to implement the list of the variables.
- After selecting the variable first in the list, update the provided values present in the Name, Value, Data Type, Description, Namespace, Expression, and Raise Change Event columns.
- Choose the variable specified in the list; further, you can click the option Move Variable to alter the scope.
- Click on the option Save Selected Items on the File menu to save the updated package.
Again, let us set the SSIS variables and their properties in the Properties window.
In SSDT (SQL Server Data Tools), visit the Integration Services project, which includes the package the user requires.
- Then right-click the package in Solution Explorer to open it.
- On the menu View, click on the option Properties Window.
- Go to the SSIS Designer and select the Package Explorer tab; the package node should be expanded.
- For altering variables having package scope, you need to enlarge the Variables node; then, the Executables nodes or the Event Handlers can also be expanded until you position the Variables node, which includes the variable the user needs to change.
- Choose the variable specified whose properties are required to be modified.
- Update the variable properties that are read/written in the Properties window. Few properties for user-defined variables are only read/write.
- On the File menu, you must select Save Selected Items to save the updated package.
Use cases SSIS variables
One can implement the use cases of SSIS variables for the below purposes in Integration Services packages:
- Informing properties of package elements at the time of execution. For instance, the user can set the number dynamically of concurrent executables, which a Foreach loop container permits.
- Containing an in-memory type lookup table. For instance, a package can execute an Execute SQL job which loads a variable having data values.
- After loading the variables having data values and then applying them to identify a search condition present in a WHERE clause, for instance, in a Script task, the script can modify the value of a variable implemented by a Transact-SQL statement in an Execute SQL task.
- Also, loading a variable having an integer and implementing the value for regulating looping inside a package control flow. For instance, one can apply a variable for controlling iteration in the assessment expression available for a For loop container.
- Occupying parameter values at the run time for Transact-SQL statements. For instance, a package can implement an Execute SQL job and apply variables to set the parameters dynamically in a Transact-SQL statement.
- Constructing expressions that consist of variable values. For instance, the Deprived Column conversion can occupy a column having the outcome acquired by multiplying a variable’s value by a value of a column.
SSIS variables examples
The variable values specified can result from expressions from other variables. This expression is a part of code returning a value, including identifiers, symbols, functions, literal, and operators. Expression is useful for dynamic updates of properties at execution time and includes expression language plus an evaluator for parsing and validating rules.
- Let us see an expression‘s visual representation:
In the SSDT (SQL Server Data Tools), a minor image will be shown on the Container / Task / Connection Manager to indicate that the object is implementing an expression. In the following example, in the Connection Manager, you can view an expression used:
- Let us view SSIS Expression Builder:
This is a tool for building an expression depending on variables, constants, and functions from the property of a Container or a Task. It helps to list complete variables with parameters based on scope, showing entire functions of SSIS such as Math, Type casting, String, etc.
- Let us view SSIS Expression for Variables:
You can see the example below; the variable is centered on the expression. Here, the variable named “ParentFolder” possesses the value of the root folder for entire files. At the same time, another variable named “ProductFileName” is stated to hold the absolute value determined by the file name. Since the root folder is stored in other variables previously, then the value of the product data file is determined as below:
*
Conclusion
- The Integration Services provisions two kinds of variables: user-defined variables, which package developers determine; system variables that Integration Services determine. These variables are implemented in the parameter bindings, which the Execute SQL task applies in the SQL statements for mapping variables to parameters.
- The value of a variable is set with the help of the Value or Expression property allocated to the variable. In Integration Services, we can implement features like the Variables window and Properties window for setting the properties of a user-defined variable.
Recommended Articles
This is a guide to SSIS Variables. Here we discuss SSIS Variables, How to set SSIS variables, and examples with code implementation. You may also have a look at the following articles to learn more –
