Flat 10% & upto 50% off + 10% Cashback + Free additional Courses. Hurry up

What is SSIS?

Below three important projects help to complete the business intelligence task.

  • SSIS: SQL Server Integration Services
  • SSAS: SQL Server Analysis Services
  • SSRS: SQL Server Reporting Services

SSIS:  It helps to perform the wide range of data migration tasks and collect data  from various data sources and store them into a central location.

It performs three important things. Extraction, Transformation,  and  Loading.



Extraction (E):  Collection of data from different sources.

Transformation (T):  A different form of  data obtained from different sources and it converted according to business need.

Loading  (L):  Data warehouse contains the loaded data.

Features of SSIS

1.Studio environments



4.Event handler

1.Studio Environments

SQl Server Integration Services included two studios

SSDT:  SQL  Server Data Tools

It helps to develop the integration service  packages  and We can perform the following  jobs  in SSDT

  • Copy basic package data from source to destination
  • Complete flow control and data flow are included when to create the packages
  • We can update the properties of packages while run time
  • Deployment creating
  • Save package copies to SQL server msdb

Step 1: Open  the SQL Server data tools.

Step 2: click on File –> New –> Project


SSMS: SQL Server Management Studio

It helps to manage packages in a production environment and we can perform the following  jobs in SSMS.

  • Folder creation to organize the packages
  • With the help of Execute package utility we can run the packages and stored  on the local computer
  • Generate a command line when we run the execute package utility
  • Storing and fetching packages to and from the SQL server msdb


An SSIS package is a collection of Control flow and data flow ,control flow includes the two things such as task and data flow task and Data flow includes source, transformation, and destination.


SSIS Expression is a combination of literals, identifiers, and operators

Literals: An interpreted variable is known as literal, different type of literals are

  • Numeric literal : expression supports integral literal and nonintegral literal
  • String literal: It has Zero or more characters enclosed with quotation and also it always followed by the \ symbol.
  • Boolean literal: It has only two options true or false.

4.Event Handling

Creating event handler is similar to building a packages and SSIS event handlers are


Example:  Let us consider two excel sheets as  follows


File 1


File 2

Translate the File 1 into  second excel  i.e, File 2 sheet compatible  format

Step 1: Create  a new project

Click file –> new –> project. Select Integration service from the group.


It will open up the SSIS designer which we will use for creating and maintaining Integration service packages.


In the SSIS package folder, we can see the default package created with name “Package.dtsx”.

Step 2: Create a connection manager to excel sheet.


Select excel and click on  add,Select the browser button and choose the excel file path



Step 3: Rename the connection manager.

Right click on the connection manager and  change the name as SourceExcelManager.



Step 4: Repeat step 3 and create other connection manager and pointing to the resultant file. Change the name as DestinationExcelManager.


Step 5: Create  control flow: transfer data from source excel to destination excel.

We should select the control flow in the SSIS designer.Drag data flow task from the tool box to designer


Rename the data flow task into “source excel to destination excel transfer task”.



Flow control: It helps to define the workflow and also control the flow of execution.

Step 6: Create the data flow

It helps to define the flow of data between source and destination.

Step 7: Create Excel Source

Choose excel source from source group and place it in the designer.


Step 8: Configure Excel sheet

Double click the excel source.


Select Data Source as “SourceExcelManager”, Data Access Mode as “Table or View” and Name of the sheet as “DataSheet1”

Step 9: Create derived column.

In this toolbox from Transformation group drag Derived column to SSIS designer.

Step 10: Connect Source to Derived column


Click on this arrow and connect it to derive column.


Step 11: Configure the derived column

Double click on derived column.


Write down Derived Column Name as Name, Select expression as Title + “ “ + FirstName + “ “ + LastName
Click Ok.

Step 12: Create Excel Destination

Add excel designation in SSIS toolbox.

Step 13: Attach Derived column to excel destination.

Follow the step 10, connect derived column and excel destination.

Step 14: Configure Excel Destination

Double click on the excel destination


 Locate connection Manager to “ExcelConnectionManager”, the Data Access mode  to “Table or View” and Name of the excel sheet to “Datasheet1”.

Click on mapping


Step 15: Execute package

Press F5


We can find the output like below


Uses Of SSIS

  1. Combining data from heterogeneous data stores
  2. Populating data warehouses and data marts
  3. Cleaning and standardizing data
  4. Building BI into data transformation process
  5. Automating administrative functions and data loading
Previous Next

Download Interview Questions asked by top MNCs in 2019?

"0 Responses on SSIS Tutorial"

    Leave a Message

    100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.

    Sales Offer

    Sign Up or Login to view the Free SSIS Tutorial.