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:

Package importing data

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:

Data flow task

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!

Package working

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:

Script task

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:

Script component

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!

This page has 0 threads Add post