DTS Custom Task Fundamentals
A Data Transformation Services (DTS) custom task is implemented as an in-process COM component. To be used in DTS Designer, the custom task must be an in-process DLL. When used programmatically, the custom task can be an out-of-process executable.
All custom tasks must implement the CustomTask interface. If the custom task has a property sheet, the task also must implement the CustomTaskUI interface. For more information, see Including a DTS Custom Task User Interface.
In Microsoft® Visual Basic®, the CustomTask interface is defined by the CustomTask object from the Microsoft DTSPackage Object Library. In Microsoft Visual C++®, it is defined by IDTSCustomTask in the include file dtspkg.h.
The DTS CustomTask interface includes the following elements.
|Description property||A textual description that identifies the task in DTS Designer or a programming environment.|
|Name property||A unique identifier used by DTS to reference the task.|
|Properties collection||A reference to a collection of Property objects that defines each property of the custom task.|
|Execute method||A subprogram that performs the function of the custom task.|
As required by COM, all elements must be present, but they can be placeholders.
Because of the process Microsoft SQL Server 2000 uses to manage DTS custom tasks, you must include both a Get and Let (or Set) property procedure for all properties. Each property associated with a custom task should consist of a pair of property procedures: a property Get to retrieve the property value, and a property Let (or Set) to assign a new value.
DTS Designer uses the Description property to label the icon for the custom task. To implement Description, you save the value to which the property is set and return that value when the property is read. If you provide a placeholder for Description, the label disappears when you close the task property page or the Custom Task Properties dialog box.
In Visual Basic, if you plan to use the default properties grid, you must provide an additional Description property for the class-specific task object. Tie Description and CustomTask_Description together so that setting either the class-specific Description property or CustomTask_Description causes the values of both to be updated.
This step is necessary because the CustomTask_Description Get and Let functions implement the Task.Description property. However, Description implements CustTask.Description, where CustTask is the name you gave to your custom task. Implementing CustTask.Description also causes Description to be included in the Properties collection. The properties grid uses the Properties collection to read and update custom task properties.
If you do not plan to use the custom task in DTS Designer and you do not plan to use the Description property, you can provide a placeholder for CustomTask_Description.
The Name property identifies the Task objects in the package. Thus, it always must be implemented. To implement Name, you save the value to which the property is set and return that value when the property is read.
It is recommended that you do not expose Name, especially in a read/write mode. DTS Designer assigns a unique name to the task when the task icon is placed on the design sheet. If you change the value of Name, DTS Designer will look for the task using the old name and fail when it cannot find it.
In a DTS application, you can set or change Name before adding the Task object to the Tasks collection. However, you will need logic to detect when the user enters a name already used by another task. It is recommended that you have the application specify task names and guarantee they are unique.
The Properties collection contains Property objects that identify the properties of the custom task. You always must implement Properties, but you can use a default properties provider supplied with DTS to do so. Invoke the default by returning either NULL or Nothing, as appropriate for the programming environment, from Properties.
The default property grid displayed by DTS Designer uses the Properties collection to read and update the custom task properties. In Visual Basic, the properties of the CustomTask interface are not included in the default Properties collection. It may be necessary to add a duplicate property and tie it to the related CustomTask property, as was the case for Description.
The Execute method provides the functionality of the custom task. Use its parameters in the following ways.
|pPackage||Use this reference to the Package2 object to access other objects in the DTS hierarchy. Do not save any reference obtained through pPackage after the return from Execute.|
|pPackageEvents||Use pPackageEvents to raise package events. Check pPackageEvents for NULL/Nothing before using.|
|pPackageLog||Use pPackageLog, a reference to the PackageLog object, to write records to the server log table or to the log file. Check pPackageLog for NULL/Nothing before using.|
|pTaskResult||Set pTaskResult to a code from the DTSTaskExecResult constants before returning from Execute in order to indicate success, retry or failure.|
In a DTS application, you do not need to call Execute from the application. DTS will call it at the appropriate time. When Execute returns, task execution is complete.
Basic Custom Task
For more information about building a basic custom task, see DTS Example: Basic Custom Task in Visual Basic, DTS Example: Adding Properties and Icons in Visual Basic and DTS Example: Adding Properties and Icons in Visual C++.