SQL Server Integration Services (SSIS) - An Introduction
4.5 (138 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
682 students enrolled
Wishlisted Wishlist

Please confirm that you want to add SQL Server Integration Services (SSIS) - An Introduction to your Wishlist.

Add to Wishlist

SQL Server Integration Services (SSIS) - An Introduction

Learn the basics of SQL Server Integration Services (SSIS) - with Practical Activities. Useful for 70-763 certification.
Best Seller
4.5 (138 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
682 students enrolled
Created by Phillip Burton
Last updated 1/2017
English
Curiosity Sale
Current price: $10 Original price: $50 Discount: 80% off
30-Day Money-Back Guarantee
Includes:
  • 4 hours on-demand video
  • 2 Articles
  • 2 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I 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.
View Curriculum
Requirements
  • 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.
  • It would help if you have had some prior experience with T-SQL, but it is not essential.
Description

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.

Who is the target audience?
  • 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
37 Lectures
03:50:08
+
Introduction
8 Lectures 53:10

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

Preview 01:48

Do you already have SSIS?
00:11

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 07:50

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
13:49

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
12:34

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

Resources
00:07

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
09:03

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
07:48
+
Level 1: Creating simple Extract and Load tasks.
6 Lectures 40:46

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
06:14

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

Connection Manager
07:56

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
11:06

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
05:59

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
04:03
+
Adding a bit of Transforming
19 Lectures 01:57:47

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
06:25

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

Data Conversion
07:30

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
08:14

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

Practice Activity - Altering Data Type
04:23

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

Aggregation
04:14

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.

Multi-cast
05:18

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

Conditional Split
05:27

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
10:34

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
02:32

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

Sort
08:24

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
11:01

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
08:17

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
03:48

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

Union All
10:03

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
04:09

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

Add some auditing
07:22

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

Adding different destinations
04:09

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
02:28
+
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
06:44

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
04:53
+
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
05:12

Let's review what you have learnt.

Well done!
01:36
About the Instructor
Phillip Burton
4.4 Average rating
3,293 Reviews
46,125 Students
25 Courses
Best Selling Instructor - over 45,000 students so far

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.

He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.

His interests are working with data, including Microsoft Excel, Access and SQL Server.