MSBI # 10 – SSIS # 3–Familiar with SSIS Variables from 0 to Expert (Part–I-Introduction)

From my last continuing post on MSBI # 9– BI # 3 – Architectural Overview of SSIS,SSRS and SSAS

 

 

We can create and develop SSIS variable are as more simple as any other technology I have seen ever.Its like we are just following few easy steps and we are done with it

Before getting into variable just see which are available Data Type in SSIS Click here for Integration Services Data Types and Click here for Working with Data Types in the Data Flow

How to access variable in SSIS

Right click and click on Variable tab to access Variable list in variable plan

Option in Variable Plane

A.Add New variable

Each Variable Will have 4 aspects which as shown

 

1. Name : Name of the variable
2. Scope : In which package scope of variable will be present
3. Data type : Storage Data Type of Variable
4. Value : Actual value of Variable (Even we can specify Default value  )

B.Delete Variable

Just select first variable which you want to delete and press second button

C.Concept of System Variable

In SSIS we already have set of system variable which we can directly use in our development as

Example of system variable

 

D.Show all variable :

This option will show all variable such as user variable + System variable

E.Select Choose variable columns

This is option further clears up as follow for Data Selection

We will have more detailed explanation in next post

we can use variables in Integration Services packages for the following main purposes:

• Updating properties of package elements at run time. For example, you can dynamically set the number of concurrent executables that a Foreach Loop container allows.

• Including an in-memory lookup table. For example, a package can run an Execute SQL task that loads a variable with data values.

• Loading variables with data values and then using them to specify a search condition in a WHERE clause. For example, the script in a Script task can update the value of a variable that is used by a Transact-SQL statement in an Execute SQL task.

• Loading a variable with an integer and then using the value to control looping within a package control flow. For example, you can use a variable in the evaluation expression of a For Loop container to control iteration.

• Populating parameter values for Transact-SQL statements at run time. For example, a package can run an Execute SQL task and then use variables to dynamically set the parameters in a Transact-SQL statement.

• Building expressions that include variable values. For example, the Derived Column transformation can populate a column with the result obtained by multiplying a variable value by a column value.

Hope this Helps !!