SQL Server Integration Services (SSIS) – An Introduction
“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.
Hello, and welcome to the course. I'll introduce what you are about to learn.
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.
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).
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.
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.
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.
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.
Before we can Extract and Load, we need a data source. Let's connect to our SQL Server database.
The "E" of ETL is for Extract. We can extract data from a table into SSIS - let's find out how.
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.
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.
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
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.
Here's one way to convert the data - let's use a Data Conversion task to convert a column's data type.
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.
Over to you - let's alter the AddressID in the Address table to a string - use table Result02
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.
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.
Sometimes you just want to work on sample data. Let's find out how.
Over to you - let's aggregate the addresses by state by country.
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!).
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.
Over to you - time for you to try Percentage Sampling, and to split the results in United States and other countries.
How do you get data together from two different data sources? Let's find out using the 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.
Sometimes you need to document what you are doing. Let's add some auditing.
Let's explore the different destinations that you can have,
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
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.
Let's display a Message Box which shows the number of rows contained in that variable, and see what the Script component can do.
Now that we've finished the course, let's take a step back and see how SSIS fits into SQL server.
Let's review what you have learnt.