4.44 out of 5
4.44
1112 reviews on Udemy

SQL Server Integration Services (SSIS) – An Introduction

Learn the basics of SQL Server Integration Services (SSIS) - with Practical Activities. Useful for 70-763 certification.
Instructor:
Phillip Burton
4,730 students enrolled
English
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.

Reviews:

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.

Introduction

1
Introduction

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

2
Introduction to Udemy
3
Breaking news - 12 April 2019
4
Do you already have SSIS?
5
Downloading SQL Server back engine - the Developer edition - for free!

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.

6
Installing SQL Server back engine

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).

7
Installing SQL Server Front 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.

8
Installing Visual Studio 2017 and SSDT (SQL Server Data Tools)
9
Is your VIsual Basic a trial version? No!
10
Resources

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

11
Running SSMS and SSIS

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.

12
Looking around SSIS

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.

Level 1: Creating simple Extract and Load tasks.

1
Data Flow Task

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.

2
Connection Manager

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

3
OLE DB Source Task

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

4
OLE DB Destination

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.

5
Data Viewer

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.

6
Practice Activity - Extract and Load

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.

Adding a bit of Transforming

1
Implicit Conversions

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.

2
Data Conversion

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

3
Derived Column task

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.

4
Practice Activity - Altering Data Type

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

5
Aggregation

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

6
Multi-cast

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.

7
Conditional Split

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

8
Error handling

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.

9
Row Sampling and Percentage Sampling

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

10
Practice Activity - Aggregation

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

11
Sort

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

12
Adding an Excel source

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!).

13
Add Text source and Excel destination

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.

14
Practice Activity - Percentage Sampling and Conditional Split

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

15
Union All

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

16
Merge and solving Mapping problems

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

17
Add some auditing

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

18
Adding different destinations

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

19
Practice Activity - Different data sources, sort, merge and auditing

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

Adding Control Flow Tasks

1
Adding Variables and Row Count

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.

2
Script component

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

Congratulations

1
Bonus - An overview of SQL Server

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

2
Well done!

Let's review what you have learnt.

3
Installing Visual Studio 2019
4
Installing SSIS in Visual Studio 2019
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.4
4.4 out of 5
1112 Ratings

Detailed Rating

Stars 5
500
Stars 4
456
Stars 3
130
Stars 2
16
Stars 1
10
4f389d22e9dbe4fa139819c9d8638c79
30-Day Money-Back Guarantee

Includes

4 hours on-demand video
3 articles
Full lifetime access
Access on mobile and TV
Certificate of Completion