SSIS Tutorial for Beginners

Introduction of SSIS

SQL Server Integration Services or SSIS is a component of the Microsoft SQL Server database that is generally used to conduct a variety of integration tasks. It is a flexible and fast data warehousing ETL tool suitable to extract, load, and transform the data using the cleaning, aggregating, or merging techniques. SSIS makes it easy moving data from one database to another, and it can extract data from multiple sources like Excel files, SQL Server Databases, and DB2 databases, etc. The tutorial on SSI relies on MS  SQL Server Data Tools. SSIS includes wizards and graphical tools to perform workflow functions like email exchange, FTP operations, data sources, and destination, etc. The SSIS tutorial for beginners covers all the fast and flexible usage of SQL queries.

List of SQL queries with examples

Let’s get started with a tutorial on SSIS!

Read: SSRS Pie Chart - Having a Slice of the Pie

Why use SSIS?

SSIS is a popular ETL tool and here are a few considerable facts on why to use SSIS for your project.

• SSIS tool is just the best choice for merging data from multiple data stores.
• It automates the data loading process and administrative functions.
• It collects data from various warehouses and data marts as required.
• It helps to clean and standardize the data for future use.
• It can identify, capture, and process the data changes as soon as it happens.
• SSIS eliminates the need for hardcore programmers by automating different tasks.
• It helps in managing errors and event handling too.
• It helps in coordinating data processing, maintenance, or data analysis successfully.
• It helps in delivering powerful BI solutions through a data transformation process.
• It contains an interactive GUI that helps in transforming data quickly instead of writing heavy programs.

Try SQL Server online training for free!

History & Evolution of SSIS

When SSIS was not introduced in the market, DTS (Data Transformation Services) was used as a part of SQL Server 2000 and SQL Server 7

• SQL Server 2005: This was the year when Microsoft decided to revamp DTS (Data Transformation Services). However, instead of modifying DTS, they renamed the service with SSIS (SQL Server Integration Services).
• SQL Server 2008: A lot of performance improvements were made to the tool, and new sources were also introduced for the same.
• SQL Server 2012: This was the biggest release of the SSIS where the project deployment concept was introduced. It helps in shifting the complete project along with its packages to the server, instead of choosing any specific package.
• SQL Server 2014: In this year, not many changes were made to the SSIS, but new transformations were added through SQL Server Feature Pack.
• SQL Server 2016: In this version, you can deploy the whole project instead of targeting any specific package. The additional changes like cloud and big data implementation were also made to the catalog.

Before moving ahead, evaluate your skills with this easy quiz now!

Read: Microsoft Power BI Tutorial For Beginners

Salient Features of SSIS

• Studio Environment, Packages, Event handler, and Expressions
• Integration functions are relevant.
• The implementation speed is quite effective.
• It can be integrated with other Microsoft SQL Family tightly.
• It supports data mining query transformation.
• It has a fuzzy lookup and grouping transformations.
• It has excellent data connectivity features like connectivity to SAP or Oracle, etc.

Studio Environments in SSIS

There are two studio environments in SSIS: SSDT and SSMS.

SSDT (SQL Server Data Tools):

It helps in developing the integration of service packages. Here are a few highlights of the tool:

• It helps in copying basic package data from the source to the destination
• When creating packages, it helps with excellent data flow and complete flow control management.
• It helps in updating the properties of packages during the run time.
• It helps in the quick deployment of packages.
• It saves the backup copy of packages for future use.

Read: How to Increase the Speed of SQL Query Execution

SSMS (SQL Server Management Studio):

it helps in managing packages in a production environment. Here are a few highlights of the tool:

• It creates folders to organize packages.
• It helps in storing and running packages to the local computer.
• It generates a command line when we execute the package utility.
• It stores and fetches packages to and from the SQL Server msdb.

SSIS Packages and Expressions

1. An SSIS package is a perfect combination of the data flow and control flow. Data flow includes the source, destination, and transformation. At the same time, control flow includes tasks and data flow tasks.
2. SSIS expression is a combination of operators, literals, and identifiers. An interpreted variable is named as the literal, and it can be divided into the following categories: Numeric Literals, String Literals, and Boolean Literals.

Read: What Is The Difference Between Having And Where Clause In SQL?

SSIS Architecture

The four major components of the SSIS Architecture can be given as:

Control Flow: It is the brain of an SSIS package that helps you in arranging the order of execution for its all components. The component contains tasks and containers further that can be managed through precedence constraints.

Read: Introducing SSIS Architecture & DW Concepts Overview

• Precedence Constraints: These are the package components that direct tasks to execute in a given order. It also defines the flow of the entire SSIS package in the best way. It helps in controlling the execution of two connected links by executing the destination task based on earlier tasks where business rules are defined using special expressions.
• Tasks: It is an individual unit of the work that works similar to function and procedure in a programming language. However, SSIS does not use any coding methods. You just have to drag or drop techniques to design surfaces and configure them.
• Containers: When tasks are grouped into units of work, they become a container. It offers visual consistency and helps in declaring variables in the scope of a specific manner. The four common types of containers can be given as Sequence Containers, For Loop Containers, and For Each Loop Containers.

Read: How to Increase the Speed of SQL Query Execution

Sequence Containers allows you to organize subsidiary tasks by grouping them, for Loop Containers offer the same functionality except that it allows you to run tasks multiple times. For each loop container also allows looping, but the difference is that instead of using any conditional expressions, it is performed over a set of objects like files are stored in a folder.

Data Flow: The main objective of the SSIS ETL tool is to extract the data, transforming the data, and loading it to another database. If control flow is the brain, then the data flow is the heart.

Packages: Another important component of SSIS architecture is a package. An SSIS package is a perfect combination of the data flow and control flow. Data flow includes the source, destination, and transformation. At the same time, control flow includes tasks and data flow tasks.

Parameters: Parameters behave like a variable within a few main exceptions. It is set outside the package easily. It can be defined as the value that should be passed in for the package to start.

How to create an Event Handler in SSIS?

The creation of an event handler is similar to building a package in SSIS. Here are a few event handlers in SSIS that you should know:

OnError, ONexecStatusChanged, OnInformation, OnPostExecute, OnPostValidate, OnPreExecute, OnProgress, OnTaskFailed, OnVariableValue

Take the example of two excel sheets  to understand the concept. You have to translate the File 1 into the second excel file that is File 2 should be converted to a compatible format. Here are the steps to follow when creating an event handler in SSIS.

Read: Top 50 Informatica Interview Questions and Answers

• Step 1 – Create a New Project by clicking on File -> New -> Project and select the integration services for the group. It will open the SSIS designer that can be used to create and maintain integration service packages. Also, in the SSIS Package folder, you can see the default package with the name “Package.dtsx.”
• Step 2 – In the second step, you should create a connection manager for the excel sheet. Select the excel sheet and click on the add Now, select the browse button and choose the excel file path.
• Step 3 – Once the connection manager is created for the excel sheet, now you should change its name. Right-click on the connection manager and rename it as a source connection manager.
• Step 4 – Now repeat the same step and create one more connection manager for the resultant file. Change the name and rename it as a destination connection manager.
• Step 5 - Create a Control Flow to transfer the data from the source file to the destination file. You should select the control flow in the SSIS designer. Now drag the data flow task from the toolbox to the designer. Rename the data flow task something logical here.
• Step 6 – Create a Data Flow to set the flow of the data among source and destination excel file.
• Step 7 – In the next step, you should create an excel source. For this purpose, choose the excel source from the source group and put it to the designer.
• Step 8 - It is time to configure the excel sheet. Now double click on the excel source to make settings as per your requirements.
• Step 9 – Create a derived column, connect the source to the derived column, and configure the derived column.
• Step 10 – create an excel destination, connect a derived column to the excel destination, and configure the excel destination. Now execute the pages. And you can see that whole data is copied to the second excel file as expected. Here is the output for your reference:

Read: 117 Most Frequently Asked RDBMS Interview Questions And Answers

A Range of SSIS Tasks

In SSIS, a task is added to manage the control flow. Here are different types of SSIS tasks that perform a different type of work. Let us have a quick discussion on each of the SSIS tasks one by one:

1. Execute SQL Task: It executes the SQL statement against a relational database system.
2. Data Flow Task: It reads the data from one or more sources, transforms the data, and writes it out against multiple destinations.
3. Analysis Service Processing Task: It is the task to process objects of a tabular model as an SSAS
4. Execute Package Task: It is used to execute one or more packages within a single project.
5. Execute Process Task: This task is used to specify command-line parameters.
6. XML Task: As the name suggests, this task helps you to merge, format, and split an XML file.
7. Web Service Task: This task is used to execute a method on a web service.
8. Script Task: It is used to run C# and VB.net coding in a visual studio environment.
9. WMI Event Watcher Task: The task allows SSIS packages to wait and respond to certain WMI events.
10. Bulk Insert Task: it is used to load bulk data into tables using Bulk Insert command.
11. Send Email Task: it is used to send emails to notify users that your package is finished or there is some error as well.
12. FTP Task: It is used to perform basic FTP functionalities.
13. File System Task: It is used to perform manipulations on file systems  like moving, renaming, deleting, or creating directories.

Read: Future Growth of a SQL Server Developer

Other Important ETL Tools

• SAP Data Services
• OWB (Oracle Warehouse Builder)
• SAS Data Management
• Power Center Informatica
• IBM Infosphere Information Server
• Sargent Data Flow
• Elixir Repertoire for Data ETL

Pros and Cons of using SSIS

Benefits of SSIS:

The tool has the following advantages.

• It supports broad documentation in different styles.
• It is easy to use, and implementation speed is also effective.
• It is tightly integrated with Visual Studio and SQL
• It offers message-based capabilities in real-time.
• It supports the distribution model as needed.
• It helps you in removing the network as a bottleneck for the data insertion by SSIS into SQL.
• It is using the SQL Server destination for the fast transfer of the data.

Read: What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)

Disadvantages of SSIS:

The tool has the following disadvantages that you should know.

• It may create issues in non-windows environments sometimes.
• The vision and strategy of the tool are not clear.
• It lacks support for alternative data integration styles.
• It may create issues when integrated with other products.

Best Practices of Using SSIS

The SSIS tutorial beginners provide basic and advanced level concepts of SSIS. Let’s learn some best practices for using SSIS.

• SSIS integration service is an in-memory pipeline. So, make sure that all transformations occur in memory.
• If possible, you should try to minimize logged operations.
• The capacity should be planned by understanding resource utilization capabilities.
• Make sure that SQL lookup transformation, destination, and the data source is optimized.
• It should be scheduled and distributed correctly.

Enroll yourself now in SQL SSIS online training.

Summary

• SSIS or SQL Server Integration Services is a component of Microsoft that was proposed in 2005 and an advancement of older versions.
• SSIS is s popular ETL tool that can be used for conducting various data integration tasks as per the requirement.
• SSIS tool can be used to merge data from multiple data stores, transform it into meaningful information, and load to other data sources.
• A few popular SSIS versions are 2005, 2008, 2012, 2014 and 2016. The latest version is SQL Server 2016 that includes almost all features and advancements. 

SQL Server versions

• SSIS tool has a wide range of features, and the most important ones are studio environments, excellent implementation speed, and robust event handling mechanisms.
• SSIS architecture has five major components. These are control flow, data flow, package explorer, event handler, and parameters, etc.
• A few popular SSIS tasks are Execute SQL Task, Data Flow Task, Analysis Services Processing Task, Execute Package Task, Execute Process Task, File System Task, FTP Tasks, Send Mail Task, Web Service Task, etc.
• It gives broad documentation support and widely used by industries.
• The major drawback of the tool is that it lacks in supporting alternative data integration styles.
• SSIS integration service is an in-memory pipeline. So, make sure that all transformations occur in memory.

Read: Top 75 SSAS Interview Questions and Answers For Beginners & Experienced Professionals

Final Words

In this blog for the SSIS tutorial for beginners guide, we discussed everything about integration service from basics to advanced level. We learned the architectural components, benefits, and drawbacks of using SSIS, best practices of using SSIS, and a practice example for creating an event handler in SSIS. This is how the tutorial on SSIS is written to make you understand the concepts of SSIS. These days ETL tools are high in demand, and ETL developer is a cool profile to start a career in the IT domain. If you want to learn about ETL tools and how can we use them at the workplace then join the SQL Server Certification course from JanBask training and master the database concepts from scratch. Put a request for demo class now and change your career graph right away with the right education and skills. All the best!

Read: Normalization in SQL | 1NF, 2NF, 3NF and BCNF with Examples

FaceBook Twitter Google+ LinkedIn Pinterest Email