SQL Essentials Training & Certification
- 6k Enrolled Learners
- Self Paced
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:
Let’s get started.
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.
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.
Moving ahead in this SSIS Tutorial, let see what exactly it is and how does it work.
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.
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.
SSIS consists of three major components, namely:
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:
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 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: Extraction is the process of extracting the data from various homogeneous or heterogeneous data sources based on different validation points.
Transformation: In transformation, entire data is analyzed and various functions are applied on it in order to load the data to the target database in a cleaned and general format.
Load: Loading is the process of loading the processed data to a target data repository using minimal resources.
Let’s have a look at the installation process.
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:
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.
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:
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.