• Execute SSIS package using BIDS :

During the development phase of the project developers can test the SSIS package execution by running the package from BIDS.

Open BIDS, configure a new package     ----------------->

Press Alt+Ctrl+L for solution explorer    ----------------->

Select the package    ----------------->

Right click and select execute package option


  • Execute SSIS package using DTEXEC.EXE :

Commend Line utility

Using the DTEXEC command line utility can execute an SSIS package that is stored in File system or SQL Server.

For example: DTEXEC.EXE /F “C:\packages\check points .dtsx”

  • Executes SSIS package using DTEXEC UI.EXE :

Using the execute package utility graphical inter phase can executes an SSIS package that is stored in the file system or SQL server.

In command line type DTEXEC UI.EXE and press enter which will open up execute package utility editor.

In execute package utility editor select general tab,

Package Source – File System

Package – Click Browser    ----------------->

Select any package from the list and click open    ----------------->

Click execute to execute the linked/ embedded package.


  • The execute package utility is also used when you execute the SSIS package from integration services node in SQL server management studio.

Open SQL Server Management Studio    ----------------->

Connect to integration services    ----------------->

Expand stored packages    ----------------->

Expand file system

Select file system and right click, Select import

Package editor

Package location – file system

Package – click browse    ----------------->

Select any package from

The list and click open    ----------------->

Package name - place cursor    ----------------->

Click ok    ----------------->

Select imported package    ----------------->

Right click and select run package    ----------------->

In execute package utility editor click execute

Inclined to build a profession as MSBI Developer? Then here is the blog post on

MSBI Training Program.

(5)  Execute ssis package using Sql server agent (using jobs):

Open Sql server management studio    ----------------->

Connect to data base engine    ----------------->

Select Sql server agent

Note:  Ensure that Sql server agent service agent service is start mode

Select Jobs    ----------------->

Right click and select new job

Provide Job Name as Load for each loop container    ----------------->

Select Steps page    ----------------->

Click new    ----------------->

Step Name - Load for each loop container

Type   - SQL Server Integration Service Package    ----------------->

In General Tab,

Package Source - File System

Package – click Browse    ----------------->

Select any package from list of package    ----------------->

Click open    ----------------->

Click ok    ----------------->

Select Schedule package    ----------------->

Click new    ----------------->

Provide the job Name as Load For each loop –Container    ----------------->

Schedule Type – Recurring    ----------------->

Set the Frequency (When to start the execution of the specified package)    ----------------->

Click ok twice

Look up Transformation: It erased to compare the data from source to reference dataset. Using Reference dataset using simple equally joint.

Note: While implementing data ware housing the reference data must be dimension table.

Steps to implement Look Up Transformation:

Open Business Intelligence development studio    ----------------->

Create a new package and rename it as look up .dtsx     ----------------->

dn control flow drag and drop  and flow task    ----------------->

dn data flow, drag and drop  OLEDB Source    ----------------->

Double click on OLEDB Source to edit it     ----------------->

Provide Connection Manager if exists and select

Production .product category    ----------------->

Select columns and click ok

Drag and drop look up Transformation and make

A connection from source to lookup    ----------------->

Provide connection manager and select production

.product sub category    ----------------->

Click configures error output and set Redirect Rows under error header    ----------------->

Click ok    ----------------->

Click ok    ----------------->

Drag and drop OLEDB destination      ----------------->

Make a connection from Look up to destination    ----------------->

Double    click on OLEDB definition to configure it    ----------------->

Provide destination connection manager and    ----------------->

Click new to create a new   destination table    ----------------->

And rename the table as Matched data    ----------------->

Click ok    ----------------->

Select Mappings    ----------------->

Drag and drop OLEDB destination to capture

UN matched records from source to reference dataset.    ----------------->

Make a connection from Look Up to OLEDB destination

Using error output (Read data flow path)    ----------------->

Click ok in look up error output editor    ----------------->

Double click on destination to configure it    ----------------->

Provide destination connection Manager and click

New to create destination table and rename it as

UN matched-records    ----------------->

Select mapping and click ok    ----------------->

Execute package


  1. Matched records will be updated to the destination.
  2. All the unmatched records will be inserted to the destination.
  3. In Look up transformation editor remove the mappings between all the columns expert between Product category ID.

Scenario: TO the Dynamic flat file destination

In control flow drag a drop data flow Task   ----------------->

Define the following variables with respect to package.

Name Data typevalue
Uv source pathstringD:\ssis packages\packages
Uv File Namestringvendor

In Data flow drag and drop OLEDB source

Double click on OLEDB source to edit it   ----------------->

Provide Source connection manager and select production. Production sub category

From drop down list         ----------------->

Select columns                          ----------------->

Drag and drop flat file destination and make a connection from OLEDB Source to flat file       ----------------->

Double click on flat file to edit it   ----------------->

Click new to configure new flat file connection Manager  ----------------->

Select Delimited flat file format   ----------------->

Click ok  ----------------->

Connection Manager Name - Dynamic vendor flat file

Description – Dynamic vendor flat file

File Name – Type the following path and file with extension which is not available at destination.

D:\ssis Package\Package\vendor.txt  ----------------->

Select columns page   ----------------->

Click ok  ----------------->

Select Mappings page   ----------------->

Click ok, In Connection    Manager, Select dynamic vendor flat file  ----------------->

Press F4 for properties  ----------------->

Expression – click browse

Select connection string and click in expression  builder, provide the following expression which create a new flat file dynamically

@ [user:: Uv source path]+”\\”+@[user::UV File Path]  + “-”+(DT_WSTR,10)(DT_DBDATE)

@ [System::start Time] +’.txt’  ----------------->

Click ok twice  ----------------->

Close property window  ----------------->

Execute package

Script task: Script task is used to design custom interphases.

Scenario: Create a text file on every corresponding month dynamically using script task.

Define the following variables type string

Uv Source path: D:\\ssispackages\packages

Uv file name: Product category Details on

Uv Full path:  ----------------->

Drag and Drop script task  ----------------->

Double click on script task to editor configure it.  ----------------->

Select Script from left panel and set,  ----------------->

Read Only Variables – Uv Source path, Uv File Name

Read write variable – Uv Full path  ----------------->

Click Design Script, Opens MS Virtual Studio for Applications IDE,  ----------------->

Provide the following VB.Net Script to create dynamic text file the specified name.

Dim s Source path As String

Dim s File name As String

Dim s Full path As String

‘D:\ssis package\.Package

S Source path = Dts. variables (“Uv source path”).value.Tostring

‘Product category Details on sFile Name = Dts.variables(“Uv File Name”).Value.Tostring

‘D:\ssis packages\packages\product category Details on-

sFull path = ssource path +  sFile Name +”_”+Month

(Now ()) ,Tostring() +Year(Now()).Tostring()+.”txt”

Dts.variables(“Uv Fullpath”).value = sFullpath.To string()  ----------------->

Select Debug Menu and Select build to build the above Scripting  ----------------->

Select file menu and Select close and return   ----------------->

Click ok     ----------------->

Drag and drop data flow task and make a connection from script task to data flow task   ----------------->

In Data Flow Drag and Drop OLEDB Source  ----------------->

Double click on OLEDB Source to configure to   ----------------->

Provide connection Manager if exits  ----------------->

Select Product category table  ----------------->

Select columns from left pane and click ok   ----------------->

Drag and drop flat file destination and make a connection from OLEDB source to flat file destination  ----------------->

Double click on flat file destination  ----------------->

Click new to create new connection Manager  ----------------->

Select Delimited flat file format  ----------------->

Click ok   ----------------->

Provide flat file connection manager name and description if any   ----------------->

Type the following path  ----------------->

D:\ssis package\package\product category Detailson.txt   ----------------->

Select columns from left panel  ----------------->

Click ok    ----------------->

Select mapping from left panel   ----------------->

Click ok    ----------------->

To connection manager select flat file connection manager   ----------------->

Press F4 for properties and set, expression - click Browse  ----------------->

Select connection string and click browse to build the expression in expression builder   ----------------->

Expand Variables  ----------------->

Drag and Drop User:: Uv Full path   in to expression  ----------------->

Section, i.e @ [user:: Uv Full path]      ----------------->

Click ok twice   ----------------->

Close properties window   ----------------->

Execute the package

Providing Security for SSIS Package:

Protection level is in SSIS package that is used to specify how sensitive information is saved with

In the package and also whether to encrypt the package or sensitive portions of the package.

Example1 the sensitive information would be password to the Database.

Steps to Configure protection level in SSIS

Open Business Intelligence Development Studio

Create OLEDB connect with server authentication and provide

Design package

Select package in control flow, right click

Select properties,

  • Security

Protection level – Don’t save Sensitive

Don’t save Sensitive:

When you Specified Don’t Save Sensitive as the protection level, any sensitive information

Is not written to the package XML file when you save the package. This could be useful when

You want to make sure that anything sensitive is excluded from the package before sending

It to someone. After saving the package with this sending, open the OLEDB Connection Manager,

The Password is black even though save my password check box is checked.

Encrypt Sensitive With User Key:

Encrypt Sensitive with User Key encrypt Sensitive information based on the credentials of the

User who created the Package.

There is a limitation with this setting, if another user (a different user than the one who created

The package and saved it) opens the package the following error will be displayed, error loading

Encrypts Sensitive with user key; failed to encrypt protection level XML load (dts. Password).

Encrypt Sensitive With Password:

The Encrypt Sensitive with password setting require a password in the package and that

Password will be used to encrypt and decrypt the Sensitive information in the package. To fill in the

Package password clicks on the button in the package password field of the package and provide

Password and confirm password. When you open a package with this setting you will be prompted

To enter the password.

Note: The Encrypt Sensitive with Password Setting for the Production level property over comes

The limitation of the encrypt Sensitive with user key setting by allowing any user to open the package

As long as they have to password.

Encrypt All With Password:

The Encrypt All with password Setting used to encrypt the entire content Of the SSIS package with the specified password. You specify the package Password in the Package

Property, Same as Encrypt Sensitive with password settings. After saving the package you can

View the package XML code that is already encrypted in between encrypted data tags in the package XML.


Encrypt All With User Key:

The   Encrypt All with User Key Setting is used to encrypt the entire

Contents of SSIS package by using User Key this means that only the user who created the package

Will be able to open it, view or modified it and run it.

Server Storage:

The server storage Settings for the Production level property allows the package

To return all Sensitive information when you are saving the package to SQL server.  SSIS packages Saved to SQL Server use the MS DB Data base.

Pivoting and Un pivoting:

The presentation of the data is required for easy analysis turning columns in to rows

And rows in to columns are another way of presentation of data. So that the end user can understand

It easily .

Un pivot:

A process of turning   columns to Rows is known as Un pivot.

Steps to Configure Un pivot

Prepare the following Excel Sheet

2009Phones and components400800400300

Learn more about MSBI Interview Questions in this blog post.

Open Business Intelligence Development Studio

Create a new package and rename it as UN pivot. dtsx

In control flow drag and drop Data Flow Task

In Data Flow drag and drop Excel source

Double click on Excel source to configure it    ----------------->

Click ok   ----------------->

Click Browse   ----------------->

Select Unpirot.xls file and click open   ----------------->

Click ok   ----------------->

Select sheet 1 from the drop down list     ----------------->

Select columns and click ok   ----------------->

Drag and drop Un pivot transformation and make a connection from the excel source to Un pivot    ----------------->

Double click on Un pivot    ----------------->

Select the below columns to un pivot then, Jan, Feb, March, April, May, June    ----------------->

Rename pivot key value column Name as – Months    ----------------->

Specify sales amount as a Derived on Destination column for all the selected pivoted Key values or input columns    ----------------->

Click ok    ----------------->

Make sure that Excel source file is closed.    ----------------->

Drag and drop Excel destination   ----------------->

Make a connection from Un pivot to Excel destination    ----------------->

Double click on Excel destination to configure it   ----------------->

Click New to create new destination excel sheet and rename it as Un pivot data    ----------------->

Click ok    ----------------->

Select Mappings     ----------------->

Execute package

Pivot:  A   process of turning rows to columns is known as pivot



Steps to configure pivot

Prepare the following Excel sheet for source data.

YearQuarterSales Amount


Rename the Excel Pivot.xls    ----------------->

Open Business Intelligence Development Studio      ----------------->

Create a new package and rename it as Pivot. dtsx      ----------------->

In control flow drag and drop data flow Task.     ----------------->

In Data Flow Drag and Drop Excel source    ----------------->

Double click on Excel source to configure it    ----------------->

Click New   ----------------->

Click Browse     ----------------->

Select pivot .xls and click open   ----------------->

Click ok    ----------------->

Select sheet 1 from the drop down list    ----------------->

Drag and Drop pivot transformation and make a connection from Excel source to Pivot    ----------------->

Double click on Pivot      ----------------->

Select Input columns tab and check all input columns    ----------------->

Select input output properties tab   ----------------->

Expand pivot default output    ----------------->

Expand Input column    ----------------->

Select Year and Set, pivot usage – 1     ----------------->

Select Quarter input column and Set, pivot usage – 2

Select Sales Amount input column and set, pivot usage – 3

Pivot Usage:

Pivot Usage tells ssis how to treat the data what its role during the

Transformation process

0 – The column is not pivoted1

  1. The column is part of the set key that identifiers 1 or more rows as part of 1 set.

All input rows with the same set key are considered in to 1 output row.

  1. The column is pivot column
  2. The values from this column are Placed in columns that are created as a result of the pivot .    ----------------->

Export pivot default output and create the following columns by click add columns button Year Q1,Q2, Q3, Q4    ----------------->

Copy or Note the Lineage ID of input column year

Select year output column and set,

Pivot Key value – Year

Source column – 58(Lineage ID of year input column)

Select Q1 and set,

Pivot Key value – Q1

Source column – 64

Select Q2 and set,

Pivot Key value – Q2

Source column – 64

And follow the same process for Q3 and Q4 output columns    ----------------->

Click Refresh    ----------------->

Click ok   ----------------->

Drag and Drop Excel destination     ----------------->

Double click on Excel destination to edit it   ----------------->

Provide Excel connection Manager,   ----------------->

Click New to create a new table (sheet) and name it as pivot data     ----------------->

Click ok twice    ----------------->

Execute package

For an Indepth knowledge on MSBI click on: