SQL Server Integration Services (SSIS) - An Introduction
4.5 (2,520 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
10,064 students enrolled

SQL Server Integration Services (SSIS) - An Introduction

Learn the basics of SQL Server Integration Services (SSIS) - with Practical Activities. Useful for 70-763 certification.
4.5 (2,520 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
10,064 students enrolled
Created by Phillip Burton
Last updated 3/2020
English, French [Auto]
Current price: $69.99 Original price: $99.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 4 hours on-demand video
  • 6 articles
  • 2 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Do Extraction, Transform and Load (ETL) tasks.
  • You can use SQL Server tables, Excel spreadsheets and text files as your source.
  • You'll be able to change data types, add columns, aggregate, split, sort and join
  • You can then view what has been created, and save it to spreadsheet, database tables, or the clipboard.
  • You don't need to have any prior experience with SQL Server.
  • You don't even need to have it installed - we'll install the full Developer edition for free on your Windows machine.
  • It would help if you have had some prior experience with T-SQL, but it is not essential.


"Instructor is easy to follow. He gives very clear examples. Course moves quickly enough to be engaging without skipping over material." -- Sarah Hale

"This course works for me because it progresses with a level of detail that will be useful as I think through each step necessary to execute my own integration packages. There are several courses to choose from and I'm glad I chose this one." -- David McNickle

"Very good introductory course. Didn't really know what SSIS was until recently. Working through the course opened my eyes to past projects that would have benefited from an SSIS ETL package or two." -- Stephen Benton

Welcome to this course about SQL Server Integration Services.

In this course you'll be downloading and installing the latest 2016 SQL Server Developer – a full version for free, so that you can use it on your machine. And previous versions of SQL Server Developer have previously retailed for around $100, and now it’s free.

So what is SSIS? It’s a way to automate in the importing and exporting and data – or ETL: extract, transform and load. It’s a very visual part of SSIS, using tasks, components and flowcharts, so it’s easy to learn the basics of it.

We’ll start by extracting some data, and then loading it into another table. I’ll take nice as slow as we start to introduce transformations, such as aggregation, conditional split, merging and auditing. There’ll be plenty of practice activities too, so you can get to practice your new-found skills. And if you are doing Microsoft certificates, it can help towards your 70-463 certification as well.

By the end of this course, you'll be confident in using the main Data Flow components and Control Flow tasks, and would be able to use it in your own work.

Who this course is for:
  • You should take this course if you are not experienced in SSIS, or if you want to know what it is for.
  • SSIS is good for automating certain processes, and is very visible.
  • It is great for beginners, and good if you have had a little prior experience.
  • As it is going through the basics, it is not useful if you commonly use SSIS.
  • It can help towards your 70-463 certification.
Course content
Expand all 47 lectures 03:59:52
+ Introduction
4 lectures 04:33

Hello, and welcome to the course. I'll introduce what you are about to learn.

Preview 01:48
Welcome to Udemy
Introduction to Udemy
Do you already have SSIS?
+ Installing SSIS in Visual Studio 2019
10 lectures 48:15

It used to be that we had to install a cut-down version of SQL Server. Instead, let's now install a version with the full functionality of the Enterprise edition - for personal use only, though.

Preview 06:09

Now let's go through the process of installing SQL Server.  I'll also go through the various editions of SQL Server (e.g. 2008, 2012).

Installing SQL Server back engine

Now that's the back engine has been installed, we've got to install SSMS (SQL Server Management Studio), Visual Studio, and SSDL (SQL Server Data Tools). It takes around 30 minutes, but here's the edited version.

Installing SQL Server Front Engine
Installing Visual Studio 2019
Installing SSIS in Visual Studio 2019
Is your Visual Studio a trial version? No!

Here are the resources you'll need for this course.


You've probably run SQL Server Management Studio before - and we'll do so again, and install our sample database. However, how on earth do you run SSIS? And what is BIDS, SSDL or Visual Studio? Let's find out that they are basically one and the same.

Running SSMS and SSIS
Can't see the SSIS Toolbox?

We'll have a look at all of the various toolbars that we have, and see how we can customise, hide, show and resize them. We'll create our first package, and have a look at how the Toolbox can change, depending on which tab we are in.

Looking around SSIS
+ Level 1: Creating simple Extract and Load tasks.
8 lectures 41:34

To do the specifics of ETL, we need to add a data flow task within the Control Flow. Let's add one, and find out what happens.

Data Flow Task
Problems with Connection Manager

Before we can Extract and Load, we need a data source. Let's connect to our SQL Server database.

Connection Manager
Problems with Connection Manager - reminder

The "E" of ETL is for Extract. We can extract data from a table into SSIS - let's find out how.

OLE DB Source Task

The "L" of ETL is for Load. Let's take the information that we had from the OLE DB Source Task, and insert it into another table. Don't forget - given that this is a flowchart, we need to connect to the tasks together.

OLE DB Destination

Wouldn't it be good to see what is happening internally? Let's add a data viewer and find out what goes between the two tasks.

Preview 05:28

And now the first of your Practice Activities. Why not see if you can extract the Address table and load it into the table Result01, and add a data viewer.

Practice Activity - Extract and Load
+ Adding a bit of Transforming
19 lectures 01:58:40

We'll alter the destination table, and then we'll find that the input and output columns aren't of the same data type. Let's see whether SSIS spots this discrepancy, and how it is corrected.

Implicit Conversions

Here's one way to convert the data - let's use a Data Conversion task to convert a column's data type.

Data Conversion

Here's another way of changing the data type. Let's add a new column into our data which converts (transforms) the data, and then we can use this derived column in the destination output.

Derived Column task

Over to you - let's alter the AddressID in the Address table to a string - use table Result02

Practice Activity - Altering Data Type

This is the equivalent of GROUP BY in T-SQL. Let's find out how to summarise data.


Suppose that we didn't want to put our output into one table, but into two. Multi-cast allows us the duplicate our source - let's try it.


What if we needed to split the input - perhaps into a male table and a female table. Let's find out how.

Conditional Split

Something went wrong in our Conditional Split. As yet, we don't know what. Let's find out how to handle errors, and what we can do with them.

Error handling

Sometimes you just want to work on sample data. Let's find out how.

Preview 03:29

Over to you - let's aggregate the addresses by state by country.

Practice Activity - Aggregation

The results of our last Practice Activity was in an odd order. Let's sort it.


Let's introduce an Excel source, and find out that it doesn't necessarily work first time for modern computers - and how to solve this problem (hopefully!).

Adding an Excel source

We can also import sources from text files. Let's do so, and save it as an Excel file, and find out why sometimes we need to do a Data Conversion.

Add Text source and Excel destination

Over to you - time for you to try Percentage Sampling, and to split the results in United States and other countries.

Practice Activity - Percentage Sampling and Conditional Split

How do you get data together from two different data sources? Let's find out using the Union All.

Union All

The merge component is not very useful - but let's use it to see how mapping errors occur, and how to resolve them.

Merge and solving Mapping problems

Sometimes you need to document what you are doing. Let's add some auditing.

Add some auditing

Let's explore the different destinations that you can have,

Adding different destinations

For your practice activity, let's use Source01 and Source02. Then combine them, add some auditing, and export it to Excel.

Practice Activity - Different data sources, sort, merge and auditing
+ Adding Control Flow Tasks
2 lectures 11:37

It's good to know through the data viewer how many rows there were - but can we get this information programmatically. Let's get the number of rows - but before we can do that, we need to work out how to use variables. And how do we see the results of this variable? Let's add a breakpoint, and then add a watch for that variable.

Adding Variables and Row Count

Let's display a Message Box which shows the number of rows contained in that variable, and see what the Script component can do.

Script component
+ Congratulations
2 lectures 06:48

Now that we've finished the course, let's take a step back and see how SSIS fits into SQL server.

Bonus - An overview of SQL Server

Let's review what you have learnt.

Well done!
+ Old Video - Installing Visual Studio 2017
2 lectures 08:24
Installing Visual Studio 2017 and SSDT (SQL Server Data Tools)
Is your VIsual Basic a trial version? No!