Export (0) Print
Expand All

Adding Tasks Programmatically

Tasks can be added to the following types of objects in the run-time engine:

These classes are considered containers, and they all inherit the Executables property. Containers can contain a collection of tasks, which are executable objects processed by the runtime during execution of the container. The order of execution of the objects in the collection is determined any PrecedenceConstraint set on each task in the containers. Precedence constraints enable execution branching based on the success, failure, or completion of an Executable in the collection.

Each container has an Executables collection that contains the individual Executable objects. Each executable task inherits and implements the Executable.Execute method and Executable.Validate method. These two methods are called by the run-time engine to process each Executable.

To add a task to a package, you need a container with an Executables existing collection. Most of the time, the task that you will add to the collection is a package. To add the new task executable into the collection for that container, you call the Executables.Add method . The method has a single parameter, a string, that contains the CLSID, PROGID, STOCK moniker, or TaskInfo.CreationName of the task you are adding.

Although you can specify a task by name or by ID, the STOCK moniker is the parameter used most often in the Executables.Add method. To add a task to an executable identified by the STOCK moniker, use the following syntax:

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

The following list shows the names for each task that are used after the STOCK moniker.

  • ActiveXScriptTask

  • BulkInsertTask

  • ExecuteProcessTask

  • ExecutePackageTask

  • Exec80PackageTask

  • FileSystemTask

  • FTPTask

  • MSMQTask

  • PipelineTask

  • ScriptTask

  • SendMailTask

  • SQLTask

  • TransferStoredProceduresTask

  • TransferLoginsTask

  • TransferErrorMessagesTask

  • TransferJobsTask

  • TransferObjectsTask

  • TransferDatabaseTask

  • WebServiceTask

  • WmiDataReaderTask

  • WmiEventWatcherTask

  • XMLTask

If you prefer a more explicit syntax, or if the task that you want to add does not have a STOCK moniker, you can add the task to the executable using its long name. This syntax requires that you also specify the version number of the task.

      Executable exec = package.Executables.Add(
        "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, " +
        "Microsoft.SqlServer.ScriptTask, Version=10.0.000.0, " +
        "Culture=neutral, PublicKeyToken=89845dcd8080cc91");

You can obtain the long name for the task programmatically, without having to specify the task version, by using the AssemblyQualifiedName property of the class, as shown in the following example. This example requires a reference to the Microsoft.SqlServer.SQLTask assembly.

using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
...
      Executable exec = package.Executables.Add(
        typeof(Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask).AssemblyQualifiedName);

The following code example shows how to create an Executables collection from a new package, and then add a File System task and a Bulk Insert task to the collection, by using their STOCK monikers. This example requires a reference to the Microsoft.SqlServer.FileSystemTask and Microsoft.SqlServer.BulkInsertTask assemblies.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.FileSystemTask;
using Microsoft.SqlServer.Dts.Tasks.BulkInsertTask;

namespace Microsoft.SqlServer.Dts.Samples
{
  class Program
  {
    static void Main(string[] args)
    {
      Package p = new Package();
      // Add a File System task to the package.
      Executable exec1 = p.Executables.Add("STOCK:FileSystemTask");
      TaskHost thFileSystemTask = exec1 as TaskHost;
      // Add a Bulk Insert task to the package.
      Executable exec2 = p.Executables.Add("STOCK:BulkInsertTask");
      TaskHost thBulkInsertTask = exec2 as TaskHost;

      // Iterate through the package Executables collection.
      Executables pExecs = p.Executables;
      foreach (Executable pExec in pExecs)
      {
        TaskHost taskHost = (TaskHost)pExec;
        Console.WriteLine("Type {0}", taskHost.InnerObject.ToString());
      }
      Console.Read();
    }
  }
}

Sample Output:

Type Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask

Type Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask

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.

The following code example shows how to cast a TaskHost, thBulkInsertTask, that contains a BulkInsertTask, to a BulkInsertTask object.

      BulkInsertTask myTask = thBulkInsertTask.InnerObject as BulkInsertTask;

The following code example shows how to cast the executable to a TaskHost, and then use the InnerObject property to determine which type of executable is contained by the host.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.FileSystemTask;
using Microsoft.SqlServer.Dts.Tasks.BulkInsertTask;

namespace Microsoft.SqlServer.Dts.Samples
{
  class Program
  {
    static void Main(string[] args)
    {
      Package p = new Package();
      // Add a File System task to the package.
      Executable exec1 = p.Executables.Add("STOCK:FileSystemTask");
      TaskHost thFileSystemTask1 = exec1 as TaskHost;
      // Add a Bulk Insert task to the package.
      Executable exec2 = p.Executables.Add("STOCK:BulkInsertTask");
      TaskHost thFileSystemTask2 = exec2 as TaskHost;

      // Iterate through the package Executables collection.
      Executables pExecs = p.Executables;
      foreach (Executable pExec in pExecs)
      {
        TaskHost taskHost = (TaskHost)pExec;
        if (taskHost.InnerObject is Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask)
        {
          // Do work with FileSystemTask here.
          Console.WriteLine("Found task of type {0}", taskHost.InnerObject.ToString());
        }
        else if (taskHost.InnerObject is Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask)
        {
          // Do work with BulkInsertTask here.
          Console.WriteLine("Found task of type {0}", taskHost.InnerObject.ToString());
        }
        // Add additional statements to check InnerObject, if desired.
      }
      Console.Read();
    }
  }
}

Sample Output:

Found task of type Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask

Found task of type Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask

The Executables.Add statement returns an executable that is cast to a TaskHost object from the newly created Executable object.

To set properties or to call methods on the new object, you have two options:

  1. Use the Properties collection of the TaskHost. For example, to obtain a property from the object, use th.Properties["propertyname"].GetValue(th)). To set a property, use th.Properties["propertyname"].SetValue(th, <value>);.

  2. Cast the InnerObject of the TaskHost to the task class. For example, to cast the Bulk Insert task to a BulkInsertTask after it has been added to a package as an Executable and subsequently cast to a TaskHost, use BulkInsertTask myTask = th.InnerObject as BulkInsertTask;.

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.

The following code example expands on the concept of reusing task code. Instead of casting tasks to their specific class equivalents, the code example shows how to cast the executable to a TaskHost, and then uses the Properties to write generic code against all the tasks.

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace Microsoft.SqlServer.Dts.Samples
{
  class Program
  {
    static void Main(string[] args)
    {
      Package package = new Package();

      string[] tasks = { "STOCK:SQLTask", "STOCK:ScriptTask", 
        "STOCK:ExecuteProcessTask", "STOCK:PipelineTask", 
        "STOCK:FTPTask", "STOCK:SendMailTask", "STOCK:MSMQTask" };

      foreach (string s in tasks)
      {
        TaskHost taskhost = package.Executables.Add(s) as TaskHost;
        DtsProperties props = taskhost.Properties;
        Console.WriteLine("Enumerating properties on " + taskhost.Name);
        Console.WriteLine(" TaskHost.InnerObject is " + taskhost.InnerObject.ToString());
        Console.WriteLine();

        foreach (DtsProperty prop in props)
        {
          Console.WriteLine("Properties for " + prop.Name);
          Console.WriteLine("Name : " + prop.Name);
          Console.WriteLine("Type : " + prop.Type.ToString());
          Console.WriteLine("Readable : " + prop.Get.ToString());
          Console.WriteLine("Writable : " + prop.Set.ToString());
          Console.WriteLine();
        }
      }
      Console.Read();
    }
  }
}

Blog entry, EzAPI – Updated for SQL Server 2012, on blogs.msdn.com.

Integration Services icon (small)  Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft