Developing custom Data Integration components

You might want to build your own components for use in SQL Server Integration Services (IS) if you need some functionality that doesn’t exist just yet. This might be to simplify maintenance and make reuse easy, or because the task is pretty complex in itself. The components you might want to customize are:

  • Tasks
  • Transforms
  • Adapters
  • ForEach Enumerators
  • Connection Managers
  • Log Providers

Some examples of customs components are secure FTP or TAPI task, a dataflow component for Internet Log file parsing.

You have three options to build your own tasks:

  1. Script tasks: Quick and easy way to develop custom tasks that offer good performance. Note that your code is visible to everyone.
  2. Native tasks: are written native code and are needed when you want the best performance. They are fairly rare and not supported.
  3. Managed tasks: a great compromise

How to code them
Your custom IS component will be a strongly named assembly referencing Microsoft.SqlServer.PipelineHost.dll and Microsoft.SqlServer.PipelineWrap.dll (for Dataflow components) or Microsoft.SqlServer.ManagedDTS.dll and Microsoft.SqlServer.DTSRuntimeWrap.dll (for runtime components reference).

You derive your component from the appropriate base class and mark it with an attribute for enumeration by the IS runtime. For example, a new task would be like so:

using Microsoft.SqlServer.Dts.Runtime;

[DtsTask (DisplayName = “MessageBoxTask”)]
public class MyTask: Task
  public override DTSExecResult Execute(Connections connections, …)
    return base.Execute(…);
  private message = string.Empty;
  public string Message
    get { return message; }
    set { message = value; }

For convenience sake, set the output path to C:Program FilesMicrosoft SQL Server90DTSTasks. Build it and you are ready to go.

There are additional methods to override, like Initialize (for custom events and log events), Validate (these can run anytime and allow you to indicate warnings and errors). If you want to add a custom task UI you need to include a named attribute in the DtsTask attribute named UITypeName with the value set to the fully qualified name of the assembly containing the custom UI.

The tranform components (aka pipeline components) are a bit more difficult to develop, but follow the same pattern.

Loads of ideas for personal projects, like creating a set of tasks and data source components for Half-Life 1 and 2 log files. These are not easy to digest and need regular expressions for that. I’ll keep you posted.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s