SHARE

Build your own DTS CustomTask

Written By

Feb 28, 2000

2 minute read

We all use the standard DTS Tasks within our packages, but sometimes we need that little bit more. The first choice is
to write an ActiveX Script Task to do the job, and this usually suffices. Unfortunately
there are some limitations as to what you can do, the most obvious being the inability to define variables as types.
The other problem is one of maintenance, as the script must be maintained in
every package that requires this custom functionality.
This is where CustomTasks can be used. Once written and registered, a CustomTask is available for use in all
packages straight from the Designer Toolbar.

Unfortunately with the method outlined below, properties for the CustomTask are unavailable for manipulation within VBScript.

For a better introduction to CustomTasks, have a look at the
SQL Server Magazine article Constructing DTS Custom Tasks

On with the guide:

Create a new ActiveX DLL project. The sample project is called MyCustomTask, and the class is called CustomTask.

Reference the Microsoft DTS Package Object Library (Project->References menu).

You can now start adding code to your class as described below –

First you must setup the CustomTask interface via the Implements statement,
and declare the two required properties, Name & Description.
If you have any properties of your own, then declare them here as well.

Option Explicit

'Required for CustomTask
Implements DTS.CustomTask

'Required Custom Task Properties
Public Description As String
Public Name As String

'Define your own CustomTask Properties here
Public MyProperty As String

Next set up the property handling for the required properties

Private Property Get CustomTask_Name() As String
    CustomTask_Name = Name
End Property

Private Property Let CustomTask_Name(ByVal vNewValue As String)
    Name = vNewValue
End Property

Private Property Get CustomTask_Description() As String
    CustomTask_Description = Description
End Property

Private Property Let CustomTask_Description(ByVal vNewValue As String)
    Description = vNewValue
End Property

Let DTS handle your CustomTask properties for you

Private Property Get CustomTask_Properties() As DTS.Properties
    Set CustomTask_Properties = Nothing
End Property

Create the Execute Method for your CustomTask

Private Sub CustomTask_Execute(ByVal pPackage As Object, _
  ByVal pPackageEvents As Object, _
  ByVal pPackageLog As Object, _
  pTaskResult As DTSTaskExecResult)

 'Handle any Error
On Error Goto ExecuteError

 'Set Result Flag to assume success
pTaskResult = DTSTaskExecResult_Success

 'Put your processing code here
 'Put some more for luck
 '...
MsgBox "CustomTask_Execute"

    GoTo ExitCode

ExecuteError:
 'Set the Result Flag to Failure
pTaskResult = DTSTaskExecResult_Failure

ExitCode:
 'Clean up any objects here

End Sub

Using the template above you can successfully build a simple CustomTask, which is perfectly usable.
By default DTS will implement a simple grid which enables you to view and set properties of the CustomTask, but our
next stage is to enhance the usability and of the Task, by adding a user interface for these properties-
Next Page

Build your own DTS CustomTask

Darren Green

Company

Categories