This SSIS Tutorial blog will mainly focus on various aspects of SSIS. You will learn – what SSIS is, its components, features, functions, and several frequently asked questions.
The SSIS full form is SQL Server Integration Services. It is basically a component of Microsoft SQL Server database software that is used to perform data migration at scale. As a platform, SSIS enhances data integration and workflow applications.
What is SSIS?
SQL Server Integration Services is a platform for developing enterprise-level data integration and data transformation solutions. Use Integration Services to manage databases, copy files and download data, load data warehouses, cleanse and analyze data, and manage SQL Server objects.
Integrating Services extracts and transforms data from a wide range of sources, such as XML files, flat files, and relational data sources, and loads that data onto one or more destinations.
Many tasks and transformations are built into Integration Services for building packages, and you can manage them in the Integration Services Catalog Database.
Watch this SSIS Tutorial for beginners video:
Implementing ETL using SSIS Components
We can use SSIS to implement ETL, or extract, transform, and load data into a Data Warehouse.
The ETL acronym stands for Extraction, Transformation, and Loading. This process involves extracting, transforming, and loading data into the final repository. ETL is the process of loading data into the warehouse from the source system.
In this diagram, we have described the ETL process step-by-step.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20722%20336'%3E%3C/svg%3E)
-
- Extraction (E): Collection of data from different sources.
- Transformation (T): A different form of data obtained from different sources and converted according to business needs.
- Loading (L): The Data warehouse contains the loaded data.
ETL is facilitated by SSIS components, which are listed below:
-
- 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)
Features of SSIS
Now in this SSIS tutorial, let’s have a look at the key features of SSIS:
-
- Data cleansing and profiling for better quality data
- Smooth data integration from disparate data sources
- Seamless integration with other components of Microsoft SQL products
- Enriched studio environment and graphical tools/wizards
- Workflow functionalities like file transfer protocol
- APIs for SSIS object modeling
- Efficient implementation of high-speed data connectivity/integration
- Packaged data source connectors
- Organized data mining query and lookup transformation
- Master and metadata management
Key Functions of SSIS
Now, let’s understand the key functionalities of SSIS.
1. Studio Environments
SQL Server Integration Services included two studios:
- SSDT: SQL Server Data Tools
- SSMS: SQL Server Management Studio
Let’s understand each of them.
1.1 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 creating the packages.
- We can update the properties of packages during run time.
- Deployment will be created.
- Save package copies to SQL server msdb.
1.2 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 the Execute Package utility, we can run the packages and store them 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.
Now, let’s see an example of using SSIS to transfer and transform data between two Excel files.
Step-by-Step Guide to Transferring and Transforming Data Between Excel Files Using SSIS
Example: Let us consider two Excel sheets as follows:
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20475%20167'%3E%3C/svg%3E)
File 1
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20293%20184'%3E%3C/svg%3E)
File 2
Step 1: Create a New Project.
Click File –> New –> Project. Select Integration service from the group.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20459%20283'%3E%3C/svg%3E)
It will open up the SSIS designer, which we will use for creating and maintaining Integration service packages.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20606%20396'%3E%3C/svg%3E)
In the SSIS package folder, we can see the default package created with the name “Package.dtsx”.
Step 2: Create a connection manager to an Excel sheet.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20308%20283'%3E%3C/svg%3E)
Select Excel and click on “Add.” Select the browser button and choose the Excel file path
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20365%20207'%3E%3C/svg%3E)
Step 3: Rename the connection manager.
Right-click on the connection manager and change the name to SourceExcelManager.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20606%20236'%3E%3C/svg%3E)
Step 4: Repeat step 3 create another connection manager and point to the resultant file. Change the name to DestinationExcelManager.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20505%20241'%3E%3C/svg%3E)
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 the designer.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20311%20190'%3E%3C/svg%3E)
Rename the data flow task into “source Excel to destination Excel transfer task”.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20456%20118'%3E%3C/svg%3E)
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.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20211%20140'%3E%3C/svg%3E)
Step 8: Configure the Excel sheet
Double-click the Excel source.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20383%20308'%3E%3C/svg%3E)
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, the group drags the Derived column to the SSIS designer.
Step 10: Connect Source to Derived column
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20142%20129'%3E%3C/svg%3E)
Click on this arrow and connect it to derive a column.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20150%20110'%3E%3C/svg%3E)
Step 11: Configure the derived column.
Double-click on the derived column.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20456%20378'%3E%3C/svg%3E)
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 the Derived column to the Excel destination.
Following step 10, connect the derived column and Excel destination.
Step 14: Configure Excel Destination
Double-click on the Excel destination
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20381%20336'%3E%3C/svg%3E)
Locate connection Manager to “ExcelConnectionManager”, the Data Access Mode to “Table or View” and the Name of the Excel sheet to “Datasheet1”.
Click on mapping.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20383%20308'%3E%3C/svg%3E)
Step 15: Execute the package. Press F5.
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20340%20205'%3E%3C/svg%3E)
We can find the output below:
![msbi1](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20291%20168'%3E%3C/svg%3E)
This is how we have successfully transferred and transformed data from File 1 to File 2 using SSIS.
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 is always followed by the symbol.
- Boolean literal: It has only two options true or false.
4. Event Handling
Creating an event handler is similar to building packages and SSIS event handlers are:
OnError,OnExecStatusChanged,OnInformation,OnPostExecute,OnPostValidate,OnPreExecute,OnProgress,OnTaskFailed,OnVariableValueChanged,OnWarning.
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
Frequently Asked Questions
What is SSIS used for?
The following are some common uses of SSIS:
- Data archiving (export)
- Data loading (import)
- Data transfer between two data sources
- Cleansing or transforming dirty data
- DBA tasks like filtering old files or indexing databases
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.
Our Business Intelligence Courses Duration and Fees
Cohort starts on 11th Feb 2025
₹17,043
Cohort starts on 11th Feb 2025
₹17,043