SSIS Tutorial

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 a wide range of data migration tasks and collect data from various data sources and store them into a central location.

We have the perfect professional MSBI Training Course for you. Enroll now!

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 needs.

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

Features of SSIS

1.Studio environments
2.Packages
3.Expressions.
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

Interested in learning MSBI? Click here to learn more about this MSBI Training in New York!

2.Packages

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.

3.Expressions
SSIS Expression is a combination of literals, identifiers, and operators
Literals: An interpreted variable is known as a 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 the quotation and also it always followed by the \ symbol.
Boolean literal: It has only two options true or false.

Get familiar with the top MSBI Interview Questions to get a head start in your career!

4.Event Handling
Creating event handler is similar to building packages and SSIS event handlers are

OnError,OnExecStatusChanged,OnInformation,OnPostExecute,OnPostValidate,OnPreExecute,OnProgress,OnTaskFailed,OnVariableValueChanged,OnWarning.

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 the 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 another 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 toolbox 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 an Excel Source

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

Step 8: Configure the 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 a derived column.

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

Step 10: Connect Source to Derived column

Click on this arrow and connect it to derive a column.

Step 11: Configure the derived column
Double click on the derived column.

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

Step 12: Create an Excel Destination

Add excel designation in the SSIS toolbox.

Step 13: Attach Derived column to excel destination.

Follow step 10, connect the 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”.

Visit our Community to get answers to all your queries!

Click on mapping

Step 15: Execute the package
Press F5

We can find the output like below

Uses Of SSIS

Combining data from heterogeneous data stores
Populating data warehouses and data marts
Cleaning and standardizing data
Building BI into the data transformation process
Automating administrative functions and data loading

Check out the top MSBI Interview Questions to learn what is expected from MSBI professionals!

Frequently Asked Questions

What is SSIS and why is it used?

SSIS or SQL Server Integration Services is basically a component of Microsoft SQL Server database software which is used to perform data migration at scale. As a platform, SSIS enhances data integration and workflow applications.

What are the SSIS components?

The components of SSIS are:

Control Flow (for storing containers and tasks)
Data Flow (Source, destination, and transformations)
Event Handler (for managing messages and e-mails)
Package Explorer (for offering an all-in-one view)
Parameters (for fostering user interaction)

How do I start SSIS?

Click Start
Point to All Programs
Point to Microsoft SQL Server
Click SQL Server Management Studio
In the Connect to Server dialog box, select Integration Services in the Server type list
Provide a server name in the Server name box
Click Connect

Is SSIS easy to learn?

SSIS is not difficult but takes patience and a lot of time to master. To gain full proficiency in SSIS, it is advised for learners to take up the SSIS Training and Certification course. This way learners will be able to learn SSIS comprehensively from experienced instructors and at the same time validate their skills with an SSIS certification.

You can also check out our SSIS video tutorial on YouTube to learn SSIS at an elementary level.

Is SSIS an ETL tool?

SSIS is a proprietary offering from Microsoft that helps perform ETL (Extract, Transform, and Load) operations. Apart from ETL, SSIS can be used to perform several other operations including data warehousing and data pumping.

What is the difference between SSIS and SSRS?

Although both SSIS (SQL Server Integration Services) and SSRS (SQL Server Reporting Services) are parts of the SQL Server, they serve different purposes altogether. While SSIS is used for ETL, data warehousing and data pumping, SSRS is used for Report building, Report Managing, and Report Designing.

Do you need SSIS installed to run a package?

You need to install SSIS on the server where you need to run the required package, as associated binaries are required. However, the adjacent SSIS service need not be co-installed.

Previous Next