The different Integration Services (SSIS) courses explained
We publish two SSIS courses:
| Level | Notes |
|---|---|
| Two-day introduction to SSIS | This course covers the basics of using Integration Services to import, transform and export data. |
| Two-day advanced SSIS | This course is aimed at people who already know how to use SSIS, but want to know more advanced topics (and in particular, how to create script tasks and script components). |
At the moment we haven't published any dates for the advanced course, but we will consider running it as an onsite course.
Introduction to Integration Services
Here's the sort of package that you'll learn to create on this course:
This package will import all of the CSV files in a given folder into a staging table, then tidy up the results and export them to their final resting place.
The diagram above is called control flow. The task in the middle (to import the CSV file into a staging table) is called a data flow task, and might look something like this:
This data flow task contains a data conversion task to sort out badly formatted dates, and a conditional split task to divert bad data to a separate text file.
It's very satisfying when you run a package successfully, because you see screens like this!
Lots of satisfying green ticks!
If this sort of thing looks interesting and useful, consider booking a place on one of our Introduction to SSIS courses!
One of the great benefits of SSIS is that not only does it saving you having to write reams and reams of complicated SQL, but it also creates self-documenting flow diagrams like the ones above.
Advanced Integration Services course
Our introductory SSIS course shies away from two important tasks:
| Area | Task | Notes |
|---|---|---|
| Control flow | Script task | Allows you to call separate programs |
| Data flow | Script component task | Lets you write your own data transforms |
Most people won't need to know how to work with these tasks, but some people will!
SSIS gives you a choice of whether to program in C# or VB, but since the internal expression language is based entirely on C# and C# is the default choice offered, we (naturally) use C# for this course.
Here's what the script task looks like:
Here we're passing in the value of two variables, for further processing.
Here's the sort of script you might learn to write for a task like this:
public void Main()
{
// get value of two variables
int gf = Convert.ToInt32(Dts.Variables["NumberGoodFilms"].Value);
int bf = Convert.ToInt32(Dts.Variables["NumberBadFilms"].Value);
// work out total number of films
int numFilms = gf + bf;
// show results
MessageBox.Show("There are " + numFilms.ToString() + " films");
Dts.TaskResult = (int)ScriptResults.Success;
}
A script component, by contrast, usually accepts data from a source and transforms it:
A script component takes input from a source, and manipulates it in code.
Here's the sort of thing you'll learn how to write on this course for script component tasks:
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
// get the film title and Oscars won
string title = Row.Title;
int oscars = Row.OscarWins;
// return result
string desc = title + " - " + oscars.ToString();
Row.Description = desc;
}
However, the advanced course covers much more than just scripting in SSIS: have a look at the full list of course contents to see if this is the course for you!