How To -- Add a task into package #002

In SSIS, generically, all the components are separated into two types, tasks in control flow(parallel and serial) and transformations in data flow(coordination processing, execute as execution tree) in data flow. Here I will add a task into a package.

 

Some pieces of knowledge:

·         Tasks must be added into some containers at the run-time, the container are Package/Sequence/ForLoop/ForEachLoop/DtsEventHandler. They all inherit the Executables property, which means they all contain a collection of tasks. (The order of execution of the objects in the collection is determined any PrecedenceConstraint set on each task in the containers)

·         Each task which can be added in the container’s Executables collections is an executable, inherits and implements  two methods Microsoft.SqlServer.Dts.Runtime.Executable.Execute and Microsoft.SqlServer.Dts.Runtime.Executable.Validate

·         To add a task to a package, you need a container with an Executables existing collection(e.g. the package itself). Use Microsoft.SqlServer.Dts.Runtime.Executables.Add(System.String) method, you can add a new task into a package. The method has a single parameter, a string, that contains the CLSID, PROGID, STOCK moniker, or Microsoft.SqlServer.Dts.Runtime.TaskInfo.CreationName of the task you are adding.

 

 

Although the Add(String) can accept different names, but the STOCK moniker is the most used. Add a task with STOCK moniker:

 

Executable exec = package.Executables.Add("STOCK: ActiveXScriptTask");

 

List of names can follow the key work STOCK

ActiveXScriptTask/BulkInsertTask/ExecuteProcessTask/ExecutePackageTask/Exec80PackageTask/FileSystemTask/FTPTask/MSMQTask/PipelineTask/ScriptTask/SendMailTask/

SQLTask/TransferStoredProceduresTask/TransferLoginsTask/TransferErrorMessagesTask/TransferJobsTask/TransferObjectsTask/TransferDatabaseTask/WebServiceTask/

WmiDataReaderTask/WmiEventWatcherTask/XMLTask

 

And the other ways still can be used if you like

Executable exec = package.Executables.Add(

        "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, " +

        "Microsoft.SqlServer.ScriptTask, Version=10.0.000.0, " +

        "Culture=neutral, PublicKeyToken=89845dcd8080cc91");

Executable exec = package.Executables.Add(

        typeof(Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask).AssemblyQualifiedName);

 

Task Host

The TaskHost class is a container that does not appear in the graphical user interface, but is very important in programming. This class is a wrapper for every task. Tasks that are added to the package by using the Add method as an Executable object can be cast as a TaskHost object. When a task is cast as a TaskHost, you can use additional properties and methods on the task. Also, the task itself can be accessed through the InnerObject property of the TaskHost. Depending on your needs, you may decide to keep the task as a TaskHost object so that you can use the properties of the task through the Properties collection. The advantage of using the Properties is that you can write more generic code. If you need very specific code for a task, then you should cast the task to its appropriate object.

 

BulkInsertTask myTask = thBulkInsertTask.InnerObject as BulkInsertTask;

TaskHost th = package.Executables.Add(“STOCK: BulkInsertTask ”) as TaskHost;

 

Using the TaskHost class in code, instead of casting to the task-specific class has the following advantages:

• The TaskHost Properties provider does not require a reference to the assembly in the code.
• You can code generic routines that work for any task, because you do not have to know the name of the task at compile time. Such generic routines include methods where you pass in the name of the task to the method, and the method code works for all tasks. This is a good method for writing test code.

Casting from the TaskHost to the task-specific class has the following advantages:

• The Visual Studio project gives you statement completion (IntelliSense).
• The code may run faster.
• Task-specific objects enable early binding and the resulting optimizations. For more information about early and late binding, see the topic "Early and Late Binding" in Visual Basic Language Concepts.

.A Simple example, add two task into a package(managementdts.dll is needed)

 

 

class Program

    {

        static void Main(string[] args)

        {

            Package package = new Package();

 

            Executable exec1 = package.Executables.Add("STOCK:ExecuteProcessTask");

            Executable exec2 = package.Executables.Add("STOCK:BulkInsertTask");

 

            Executables executables = package.Executables;

            foreach (var exec in executables)

            {

                //we cannot cast the executable to BulkInsertTask directly

                TaskHost th = exec as TaskHost;                

                if (th.InnerObject is BulkInsertTask)

                {

                    Console.WriteLine("Find " + th.InnerObject.ToString());

                }

            }

        }

   }

 

 

Package looks like the following: