SQL Server Integration Services (SSIS) - An Introduction

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

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.

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.

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.

Hello, and thank you for joining me in this course.

In the next video, we will add an Excel source to SSIS. However, as you will see, it is not easy.

One of the solutions is to download the Microsoft Access Database Engine Redistributable. In the next video, I'll mention how to download the 2010 version. However, in of March 2024, the 2010 version is no longer available.

Therefore, you should download the 2016 version. It is available here: https://www.microsoft.com/en-us/download/details.aspx?id=54920

Thank you for reading this, and keep learning!

Phillip

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.

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.

Let's review what you have learnt.