SQL Essentials Training and Certificatio... (9 Blogs) Become a Certified Professional
AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

SSIS Tutorial For Beginners: Why, What and How?

Last updated on Nov 26,2019 16.5K Views

Aayushi Johari
A technophile who likes writing about different technologies and spreading knowledge. A technophile who likes writing about different technologies and spreading knowledge.
1 / 2 Blog from SQL Server

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.

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.

Here are a few ways to achieve Data Integration:

Data Integration-SSIS Tutorial- Edureka

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.

Extract (ETL)- SSIS Tutorial - Edureka 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.

Transform(ETL) - SSIS Tutorial - EdurekaLoad: Loading is the process of loading the processed data to a target data repository using minimal resources.


Load process ETL- SSIS Tutorial - Edureka

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.

SQL Server Install - SSIS Tutorial - Edureka

 

 

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.

SSDT Install - SSIS Tutorial - Edureka

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.

SSDT install process - SSIS Tutorial - Edureka

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.

SSDT Installation - SSIS Tutorial - Edureka

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

SSIS Package - SSIS Tutorial - Edureka

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.

Comments
0 Comments

Join the discussion

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

SSIS Tutorial For Beginners: Why, What and How?

edureka.co