SSIS Variables

Introduction

Using variable is an important factor of all SSIS Developers. We must understand how we use the variables and the scope factors of the variable. In this article I am trying to discuss about it. Hope it will be informative. Here we are using MS SQL Server 2008 BIDS.

From where we find Variable Window

1.     SSIS Menu à Variables

2.    Right click the work area and select the Variables from the shortcut menu.

Type of Variables

SSIS supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. We can create as many user-defined variables as a package requires, but we cannot create additional system variables.

Variables Window

The columns name of the variables window are described bellow

Columns Name	Description
Name	Here we specify the name of variable.
Scope	To create a variable, first decide on the scope of the variable, whether it is the entire package, a container or a specific task. A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container. If a package runs other packages by using the Execute Package task, the variables defined in the scope of the calling package or the Execute Package task can be made available to the called package by using the Parent Package Variable configuration type.
Data Type	It specifies the data type of the variables. SSIS supports the following data types. ·         Boolean ·         Byte ·         Char ·         DateTime ·         DBNull ·         Double ·         Int16 ·         Int32 ·         Object ·         SByte ·         String ·         UInt32 ·         UInt64
Values	The value of a user-defined variable can be a literal or an expression. A variable includes options for setting the variable value and the data type of the value. The two properties must be compatible: for example, the use of a string value together with an integer data type is not valid.
Namespace	Integration Services provides two namespaces, User and System. By default, custom variables are in the User namespace, and system variables are in the System namespace. We can create additional namespaces for user-defined variables and change the name of the User namespace, but we cannot change the name of the System namespace, add variables to the System namespace, or assign system variables to a different namespace.
Raise Changed Event	When the property is set to True, the On Variable Value Changed event is raised when the variable changes value.

The Tools Bar of Variables Window

Toolbar	Descriptions
Add Variable	Adds a user-defined variable.
Delete Variable	Deletes the selected user-defined variable.
Show System Variables	Toggles between a list that includes system variables and one that does not. User-defined variables are blue, and system variables are gray.
Show All Variables	Toggles between a list that includes all variables and one that includes only those variables within the scope of the package or the selected container or task. The list will include system variables only if the Show System Variables option is selected.
Choose Variable Columns	Launches the Choose Variable Columns dialog box, where you can select which information is shown in the Variables pane

Populate Variables Using Execute SQL Tasks

Drag and drop the Execute SQL Tasks in the work area of Control flow tabs. Right click the Execute SQL Tasks and select edit.

General Tab:

In the General Tab of the Execute SQL Task Editor Select our Connection to the database where we will run your SQL Statement and enter your SQL statement. The following property of the bellow mentioned figure is important.

On the Result Set tab:

In the Result Name key in the name of the field being return in your SQL Statement.  In the Variable Name select the user-define variable that we created.

Using Variables in SQL Statements

Drag and drop the Execute SQL Tasks in the work area of Control flow tabs. Right click the Execute SQL Tasks and select edit.

General tab:

Select our Connection to the database where you will run your SQL Statement. Enter our SQL Statement and placing a ? where each variable is to be used.

On the Parameter Mapping tab: 

Select the user-define variables in the Variable Name drop down which corresponded to the first variable (?) being used in your SQL Statement. Set the Data Type to NVARCHAR, since we are using a string variable. Set the Parameter Name to 0.  This indicates the order in which the parameter is appearing in the SQL statement.  We can enter them in the pane in any order we want, but the Parameter Name must contain numbers starting from 0 to however many parameters we have minus one.  So if we have two parameters we must have one Parameter Name = 0 and one Parameter Name = 1.  You can reverse the order, but they must both exist.

References

http://msdn.microsoft.com/en-IN/library/ms141085.aspx

Hope you like it.

Posted by: MR. JOYDEEP DAS