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

image

 

 

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

image

Option in Variable Plane

A.Add New variable

image

Each Variable Will have 4 aspects which as shown

image 

  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

image

C.Concept of System Variable

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

image

Example of system variable

image 

D.Show all variable :

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

image

E.Select Choose variable columns

image

This is option further clears up as follow for Data Selection

image

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 !!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: