What are the variables and configurations in SSIS?
SSIS stands for SQL Server Integration Services. It includes variables and configurations related information. It is the new data transformation standard for SQL Server 2005. I hope this article will be helpful to all beginners of SSIS.
Introduction -
Packages are configured using variables.
Package configurations allow users to make SQL Server Integration Services (SSIS) packages portable which helps users change SQL server and file connectivity information dynamically.
How variables can be used in SSIS architecture?
At runtime, a package can be configured dynamically by using variables. If variables were not there then users are required to open the package manually and has to change all the hard-coded values and settings etc. each and every time an SSIS package is deployed from the development to production environment.
Variables are extremely useful in such case because users are only required to change the values of the variables at deployment time and anything which uses those variables will in turn to be changed.
What are different types of variables used in SSIS?
1] System Variables
2] User defined Variables.
What are system variables?
System variables contain information about a package , a container , a task , an event handler.
These are read only variables.
For e.g. MachineName It contains the name of the computer on which SSIS package is running.
StartTime It has the execution start time of the SSIS package.
What is the scope of the variable?
The scope of variable is as follows:
1] An individual container
2] Package
3] System
SSIS package configuration helps in automating export, Transformation and Loading (ETL) tasks. Variables play an important role in modularizing code. It reduces repetition.
Registry entry It is intended for hosting values of individual variables or properties.
XML configuration file It is capable of storing values of multiple properties and variables. You can simply copy its configuration anywhere and use as per your requirement.
Parent package variable Whenever you are using parent-child package, this is very useful.
Environment variable It is useful to store a single property or variable value. It is simple to modify as well. While operating Active Directory environment, you have the ability to manage environment variables across multiple computers by leveraging Group Policies and Group Policy Preferences.