Understanding the SSIS Object Model ? part 1 ? Executables

[James Beresford]

I have recently been buried deep in the process of creating SSIS packages through code – a deep and interesting challenge, not least because I have had to teach myself C# and get to grips with Object Oriented Programming – a long way from my grubby VBA I was writing many years ago.

Key to the success of understanding how to build packages through code is to understand the SSIS Object Model.*Over*a series of posts I will try and explain how the Object Model looks and works. So, on with Part 1 – Executables. As I walk through this section, I will demonstrate adding an Execute SQL task to a package.

Executables

The top level object in the Object Model is a Package. However a Package is also an Executable object – along with all the SSIS Containers. These Containers are the*Sequence Container, ForEach & For Loops and also*TaskHosts (which contain Tasks such as the ExecuteSQL Task and Data Flow – more about TaskHosts in a bit). So at the top of the tree we have the Package, but below that we have Control Flow tasks.

In broad (and possibly not quite right) terms, an Executable is a part of the Object Model that can issue a command to*start an activity. It is important to note that Data Flow sources, transformations and destination are not Executables. The Data Flow Task*is – because it can*start the Data Flow – but you cannot in isolation call a Derived Column Transformation, for example. In this context it’s a bit like an engine – the Data Flow*Task is the ignition that starts things happening, but on its own any Transformation is like a*piston – vitally important but useless until the engine starts.

So, to begin the code snippets, here I will add an Executable to a Package.

*********** // Declare and create a package object
*********** PackageTestPkg = new Package();

*********** // Create an executable object
*********** ExecutableNewExe = TestPkg.Executables.Add(“STOCK:SQLTask”);

What happened above is firstly I created a new Package Object. Then, to that object I used the Executables.Add method to add an Executable to the Packages’ Executables collection, with the*moniker “STOCK:SQLTask” to define what type of Executable I was adding.

TaskHosts

TaskHostsare*the Containers for the tasks you are familiar with from the Control Flow such as the ExecuteSQL Task and Data Flow. You will never see a TaskHost through BIDS – it is a programming concept rather than anything visual. A TaskHost has an InnerObject (and more on these in the next section) which describes the details for type of task it is (i.e. ExecuteSQL Task, Data Flow, etc.).

A good way to think of a TaskHost is like an empty glass jam jar. In itself it doesn’t do much, but if you fill it with some ExecuteSQLTask Jam, you can see it’s an Execute SQL Task.

Below we cast this added Executable as a TaskHost so we can start configuring it.

*********** // Cast as TaskHost
*********** TaskHostNewTaskHost = (TaskHost)NewExe;

*********** // Give it a name
*********** NewTaskHost.Name = “I am a new Execute SQL Task”;

There’s a limited range of properties you can set at the TaskHost level which are detailed here, such as Name – these are the properties that are common to all TaskHosts. To set properties that are specific to a type of Task, you need to access its InnerObject.

InnerObjects

The InnerObject*is the jam in the TaskHost jar from my analogy above – it contains the specific stuff for a given task. So, in order to configure*a task fully, you need to access it’s InnerObject to set the properties that are specific to that type of task.

So, below we cast this TaskHost as an ExecuteSQLTask, thus filling the TaskHost jar with our tasty ExecuteSQLTask*jam*and set its SQLStatementSourceProperty:

*********** // Cast as ExecuteSQLTask
*********** ExecuteSQLTaskNewSQLTask = (ExecuteSQLTask)NewTaskHost.InnerObject;

*********** // Set a ExecuteSQLTask specific property
*********** NewSQLTask.SqlStatementSource = “And here is my SQL Statement”;

Wrap-Up

So, now you should be a little wiser as to what is required to add and configure a Task on the Control Flow – understanding how Executables, TaskHosts and InnerObjects interact.



Get More from the original blog...