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
- Name : Name of the variable
- Scope : In which package scope of variable will be present
- Data type : Storage Data Type of Variable
- Value : Actual value of Variable (Even we can specify Default value )
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 !!