SQL Server Integration Services(SSIS) form an integral part of the Microsoft SQL Server Database software. This makes the job of data management a lot easier with its various features. In this tutorial, we will dig deeper into SSIS on a conceptual level, in the following order:
- What is Data Integration?
- Why SSIS?
- What is SSIS?
- Features of SSIS
- How SSIS Works?
- Requirements For SQL Server Integration Services
- What is SSIS Package?
Let’s get started.
What is Data Integration?
Data Integration is a process in which heterogeneous data is retrieved and combined as an incorporated form and structure. For example, every company these days have to process large sets of data from varied sources. This data needs to be processed to give insightful information for making business decisions. So a simple solution would be data Integration. It will basically integrate all your data present in the different databases and combine them on the same platform. You can even check out the details of relational databases, functions, queries, variables, etc with the SQL Server Course.
Here are a few ways to achieve Data Integration:
Now that you have understood data integration, let’s see why SSIS is used. SSIS stands for SQL Server Integration Services.
Why SSIS?
- Data can be loaded in parallel to many varied destinations
- SSIS removes the need of hardcore programmers
- Tight integration with other products of Microsoft
- SSIS is cheaper than most other ETL Tools
- SIS provides GUI to transform data easily
- Build BI into a Data Transformation Process
- Robust error and event handling
Moving ahead in this SSIS Tutorial, let see what exactly it is and how does it work.
What is SSIS?
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data integration and data transformation tasks.
- Data Integration: It combines the data residing in different sources and provides users with a unified view of these data
- Workflow: It can also be used to automate maintenance of SQL Server databases and updates to multidimensional analytical data
Features of SSIS
Some of the features to use SSIS are:
- Organized and lookup transformations
- Tight integration with other Microsoft SQL family
- Provides rich Studio Environments
- Provides a lot of data integration functions for better transformations
- High-speed data connectivity
You can go through the below video which covers all the basics including data warehousing concepts which is used for data extraction, transformation and loading (ETL). It is ideal for both beginners and professionals who want to brush up their basics of MSBI.
Moving ahead in SSIS tutorial, let’s see how exactly it works.
How SSIS Works?
SSIS consists of three major components, namely:
- Operational Data
- ETL process
- Data Warehouse
These tasks of data transformation and workflow creation are carried out using ‘SSIS Package’, which will be discussed later in this blog. Moving ahead with SSIS tutorial, let’s first understand each of these components in detail:
Operational Data
An operational data store (ODS) is a database designed to integrate data from multiple sources for additional operations on the data. This is the place where most of the data used in the current operation is housed before it’s transferred to the data warehouse for longer-term storage or archiving.
ETL Process
ETL is a process to Extract, Transform and Load the data. Extract, Transform and Load (ETL) is the process of extracting the data from various sources, transforming this data to meet your requirement and then loading into a target data warehouse. ETL provides a ONE STOP SOLUTION for all these problems.
- Extract
- Transform
- Load
Extract: Extraction is the process of extracting the data from various homogeneous or heterogeneous data sources based on different validation points.
Data Warehousing
- Datawarehouse captures the data from diverse sources for useful analysis and access.
- Datawarehousing is a large set of data accumulated which is used for assembling and managing data from various sources for the purpose of answering business questions. Hence, helps in making decisions.
Requirements For SQL Server Integration Services
To work with SSIS, you have to install the following:
- SQL Server
- SQL Server Data Tools
Let’s have a look at the installation process.
SQL Server Installation
Go to the website: https://www.microsoft.com/en-au/sql-server/sql-server-downloads to install SQL Server. You can install the latest version or the previous version according to your choice.
So there are different editions of SQL Server, namely:
- Free Trial: You get a 180-day free trial of SQL Server 2017 on Windows.
- Developer Edition: It is a full-featured free edition, licensed for use as a development and test database in a non-production environment.
- Express Edition: Express is a free edition of SQL Server, ideal for development and production for desktop, web, and small server applications.
Coming up next, let’s see how to install the data tools.
SQL Server Data Tools
Go to the website: https://docs.microsoft.com/en-us/sql/ssdt/previous-releases-of-sql-server-data-tools-ssdt-and-ssdt-bi?view=sql-server-ver15 and check out the different releases provided by Microsoft. Refer the below screenshot with the recent SSDT release along with the download link.
In this tutorial, I will be installing the 15.9.1 version. So let’s begin.
Step 1: When you open the .exe file, you will be asked to restart the system before installation.
Step 2: Once you have restarted your system, you are all set to go. Just click on the “Next” button and follow the onscreen instructions.
Step 3: It will show the tools required and the features such as SQL Server Database, SSAS, SSRS and SSIS. Make sure you check all of them and click the “install” button. Refer the below screenshot for the same.
Next in SSIS Tutorial, we will discuss SSIS package and how you can create it using the above tools.
What is SSIS Package?
A package is a fundamental block where you go ahead and code in SSIS. Now “code” does not refer to any programming language, it’s the development you do. So basically your development is done inside a package. As discussed above, SSIS is essential for ETL, and the SSIS package will do the ETL process. Therefore, it is an object that implements Integration Services functionality to extract, transform, and load data. A package is composed of:
- Connections
- Control flow elements
- Data flow elements
That’s all for this SSIS Tutorial. I hope you have enjoyed reading it.
This brings us to the end of this blog. I hope you liked this Power BI tutorial blog. This was the first blog of the Power BI series. This Power BI tutorial will be followed by my next blog, which will focus on Power BI Dashboards, do read that as well.
If you wish to learn SSIS and build a career in data visualization or BI, then check out our MSBI Training Certification which comes with instructor-led live training and real-life project experience. This training will help you understand MSBI in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of “SSIS Tutorial” and we will get back to you.