EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SSIS Tutorial SSIS Variables
Secondary Sidebar
SSIS Tutorial
  • SSIS Basic and Advanced
    • SSIS Merge Join
    • SSIS Sequence Container
    • SSIS Foreach Loop
    • SSIS Toolbox
    • SSIS Package Configuration
    • SSIS Variables
    • SSIS Expression
    • SSIS For Loop
    • SSIS Replace
    • SSIS SFTP
    • SSIS Data Types

SSIS Variables

SSIS Variables

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

16

 

  • 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.

17

  • 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 –

  1. SSIS
  2. SSIS Interview Questions
  3. Ansible Variables
  4. Lua Variables
Popular Course in this category
Data Scientist Training (85 Courses, 67+ Projects)
  85 Online Courses |  67 Hands-on Projects |  660+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more