Build your own DTS CustomTask

Monday Feb 28th 2000 by Darren Green

Samples & commentary showing you how to build a CustomTask complete with User Interface.

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
     'Set the Result Flag to Failure
    pTaskResult = DTSTaskExecResult_Failure
     '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

  • As with the implementation for the CustomTask object, there is a corresponding implementation required for the user interface. Add the CustomTaskUI implementation to the declaration section of your existing Class module
    'Required for CustomTask with User Interface
    Implements DTS.CustomTaskUI

  • The CustomTaskUI object has seven methods, all of which must be defined regardless of usage
    Private Sub CustomTaskUI_Initialize(ByVal pTask As Task)
        Dim oCustomTask As MyCustomTaskUI.CustomTask
     'Set current task to a global task variable
     'This is used by the form to retrieve values
    Set oTask = pTask
     'Set the Default Task values here
    MsgBox "CustomTaskUI_Initialize"
    End Sub
    Private Sub CustomTaskUI_Help(ByVal hwndParent As Long)
     'Not Used
    End Sub
    Private Sub CustomTaskUI_Delete(ByVal hwndParent As Long)
        MsgBox "CustomTaskUI_Delete"
    End Sub
    Private Sub CustomTaskUI_CreateCustomToolTip(ByVal hwndParent As Long, _
      ByVal x As Long, ByVal y As Long, plTipWindow As Long)
     'Not Used
    End Sub
    Private Sub CustomTaskUI_New(ByVal hwndParent As Long)
     ' Called when you add the task to a Package
    MsgBox "CustomTaskUI_New"
        frmCustom.Show vbModal
    End Sub
    Private Sub CustomTaskUI_Edit(ByVal hwndParent As Long)
     ' Called when you edit the Task, i.e. Double-Click the icon
    MsgBox "CustomTaskUI_Edit"
        frmCustom.Show vbModal
    End Sub
    Private Sub CustomTaskUI_GetUIInfo(pbstrToolTip As String, _
      pbstrDescription As String, _
      plVersion As Long,   pFlags As DTSCustomTaskUIFlags)
     'Not Used
    End Sub

  • The interface is a standard Form object. Add a form to your project, ensuring the name is referenced correctly in the New and Edit methods. The examples above use a formed named frmCustom.

  • In the Initialise method, you will notice that our CustomTask object, pTask, is passed to the user defined variable oTask. It is very important that oTask is available to the Form, as it is used to gain reference to the underlying CustomTask object, from which we can read and write the CustomTask properties. To do this I recommend adding Module to your project, with the following code
    'The variables declared below are used to pass values
    'between the Class and the Form
    Public oTask As DTS.Task

    Next we must add code to the form, and finally compile and register the CustomTask - Next Page

  • For the purpose of this example we require four main objects on our form.

    ObjectNameUsage Description
    Text BoxtxtDescriptionView and Set CustomTask Description Property
    Text BoxtxtMyPropertyView and Set custom CustomTask MyProperty Property (See Page 1)
    Command ButtoncmdOKClose Form, saving Properties
    Command ButtoncmdCancelClose Form, without saving Properties

    Sample CustomTask User Interface 'frmCustom'

  • The code behind the form is fairly simple. In the Form_Load event you read the properties and display them on the form. The cmdOK_Click event is used to save the properties back to the CustomTask.
    Option Explicit
    Private Sub Form_Load()
     'Get Reference to our CustomTask
    Dim oCustomTask As MyCustomTaskUI.CustomTask
        Set oCustomTask = oTask.CustomTask
     'Set the form object values from our Task/CustomTask properties
    txtDescription.Text = oTask.Description
        txtMyProperty.Text = oCustomTask.MyProperty
        Set oCustomTask = Nothing
    End Sub
    Private Sub cmdOK_Click()
     ' Validate Properties
    If txtMyProperty.Text = "" Then
            MsgBox "Please enter a value for MyProperty", vbCritical, App.Title
            Exit Sub
        End If
     'Get Reference to our CustomTask
    Dim oCustomTask As MyCustomTaskUI.CustomTask
        Set oCustomTask = oTask.CustomTask
     'Save the form settings back to the Task/CustomTask
    oTask.Description = txtDescription.Text
        oCustomTask.MyProperty = txtMyProperty.Text
        Set oCustomTask = Nothing
        Unload Me
    End Sub
    Private Sub cmdCancel_Click()
        Unload Me
    End Sub

  • Now you can compile the DLL and Register it through Enterprise Manager.
    • Open an existing or new package
    • From the Task menu, select Register Custom Task...
    • Complete the Register Custom Task dialog and click OK
    • Your new CustomTask will now be available from the Tasks toolbar and menu

    Whilst this CustomTask does nothing more than display a few message boxes, these in themselves are useful in determining when the different events are raised. The code for this CustomTask is available for download below, as a Visual Basic 5 project.

    Please note that this program is provided without any warranty or assurances of any kind. Use it at your own risk. Commercial distribution or distribution for profit in any form is strictly forbidden without prior permission from the author.

    Download MyCustomTaskUI (~11K).

  • Mobile Site | Full Site