DTS Programming

DTS Example: Including a User Interface in Visual Basic

The following Microsoft® Visual Basic® code example implements a property page for a Data Transformation Services (DTS) custom task. The task displays the value of a global variable and supports a timeout on the display. The task closes the display, if the user has not already done so, when the timeout occurs.

This Visual Basic project consists of a custom task class, a property page form, and a runtime display form.

Custom Task Class

The custom task class, called FinalGlobal, has these features:

Implementing the FinalGlobal Class

Use the following Visual Basic code to implement the FinalGlobal class:

Implements DTS.CustomTask
Implements DTS.CustomTaskUI

Const INVAL_PROP = "Invalid property value."

Private strDescription      As String   'Task/FinalGlobal.Description property
Private strTaskName         As String   'Task/FinalGlobal.Name property
Private strGVMonitorName    As String   'FinalGlobal.GVMonitor property
Private sngDisplayTime      As Single   'FinalGlobal.DisplayTime
Private frmShowGV           As frmFinalGlobal
Private frmGVProperties     As frmFinalGVProperties
Private objTask             As DTS.Task

Private Sub CustomTask_Execute(ByVal pPackage As Object, _
            ByVal pPackageEvents As Object, ByVal pPackageLog As Object, _
            pTaskResult As DTS.DTSTaskExecResult)
'Get reference to global variable, display its value.
    Dim objPackage      As DTS.Package2
    Dim objMonitor      As DTS.GlobalVariable
    Dim blnCancel       As Boolean
    
    'Save reference to package, release parameter reference.
    Set objPackage = pPackage
    Set pPackage = Nothing
    pTaskResult = DTSTaskExecResult_Success
    
    'Get reference to global variable.
    Set objMonitor = objPackage.GlobalVariables(strGVMonitorName)
    
    'Create display form, pass GV name and value, and timeout.
    Set frmShowGV = New frmFinalGlobal
    frmShowGV.MonitorName = strGVMonitorName
    frmShowGV.MonitorValue = objMonitor.Value
    frmShowGV.DisplayTime = 1000 * sngDisplayTime
    frmShowGV.Show vbModal
    
    'Release display form after it closes.
    Unload frmShowGV
    Set frmShowGV = Nothing
        
End Sub

Private Property Get CustomTask_Properties() As DTS.Properties
'Use default Properties collection.
    Set CustomTask_Properties = Nothing
End Property

Private Property Let CustomTask_Description(ByVal strNewDescr As String)
'Implements Task.Description.
    strDescription = strNewDescr
End Property

Private Property Get CustomTask_Description() As String
'Implements Task.Description.
    CustomTask_Description = strDescription
End Property

Private Property Let CustomTask_Name(ByVal strNewName As String)
'Implements Task.Name.
    strTaskName = strNewName
End Property

Private Property Get CustomTask_Name() As String
'Implements Task.Name.
    CustomTask_Name = strTaskName
End Property

'----------------------------------------
Private Sub DisplayPropertyPage()
'Validate task reference and display property page.

    If TypeOf objTask Is DTS.Task Then
        Set frmGVProperties = New frmFinalGVProperties
        Set frmGVProperties.TaskObject = objTask
        frmGVProperties.Show vbModal
        
        DoEvents
        Set frmGVProperties = Nothing
        
    Else
        MsgBox "Invalid task reference. Unable to display property page.", _
                vbExclamation, "FinalGlobal Task"
    End If
    
End Sub

Private Sub CustomTaskUI_CreateCustomToolTip(ByVal hwndParent As Long, _
        ByVal x As Long, ByVal y As Long, plTipWindow As Long)
'CreateCustomToolTip not implemented.
End Sub

Private Sub CustomTaskUI_Delete(ByVal hwndParent As Long)
'Delete not implemented.
End Sub

Private Sub CustomTaskUI_Edit(ByVal hwndParent As Long)
'Display property page with current values.
    DisplayPropertyPage
End Sub

Private Sub CustomTaskUI_GetUIInfo(pbstrToolTip As String, _
        pbstrDescription As String, plVersion As Long, _
        pFlags As DTS.DTSCustomTaskUIFlags)
'GetUIInfo not implemented.
End Sub

Private Sub CustomTaskUI_Help(ByVal hwndParent As Long)
'Display Help screen.
    Dim strHelpText     As String
    
    strHelpText = "Specify properties for FinalGlobal custom task.  " & _
            "Task should run as last step of package." & _
            vbCrLf & vbCrLf & _
            "Enter/change task description.  " & _
            "It appears as task icon label on design surface." & _
            vbCrLf & vbCrLf & _
            "Enter name of global variable to be displayed." & _
            vbCrLf & vbCrLf & _
            "Enter display time in seconds.  Display is removed after " & _
            "this time elapses, if not already closed by user.  " & _
            "Enter 0 if display is not to be automatically removed."
    MsgBox strHelpText, vbInformation, "FinalGlobal Help"
    
End Sub

Private Sub CustomTaskUI_Initialize(ByVal pTask As DTS.Task)
'Initialize Description property if not already set, save task reference.

    If TypeOf pTask Is DTS.Task Then Set objTask = pTask
    If Description = "" Then
        Description = "Final Global Variable Display"
    End If
    
End Sub

Private Sub CustomTaskUI_New(ByVal hwndParent As Long)
'Display property page with default values.
    DisplayPropertyPage
End Sub

'----------------------------------------
Public Property Get Name() As String
'Implements FinalGlobal.Name.
    Name = strTaskName
End Property

Public Property Let Name(ByVal strNewName As String)
'Implements FinalGlobal.Name.
    strTaskName = strNewName
End Property

Public Property Get Description() As String
'Implements FinalGlobal.Description.
    Description = strDescription
End Property

Public Property Let Description(ByVal strNewDescr As String)
'Implements FinalGlobal.Description and verifies that it is non-empty.
    
    If Len(strNewDescr) > 0 Then
        strDescription = strNewDescr
    Else
        Err.Raise 1001 + vbObjectError, Me.Name, INVAL_PROP
    End If
    
End Property

Public Property Get GVMonitor() As String
'Name of global variable to monitor.
     GVMonitor = strGVMonitorName
End Property

Public Property Let GVMonitor(ByVal strNewName As String)
'Name of global variable to monitor, verify non-empty.

    If Len(strNewName) > 0 Then
        strGVMonitorName = strNewName
    Else
        Err.Raise 1001 + vbObjectError, Me.Name, INVAL_PROP
    End If
    
End Property

Public Property Get DisplayTime() As Single
'Timeout for display form.
    DisplayTime = sngDisplayTime
End Property

Public Property Let DisplayTime(ByVal sngNewTime As Single)
'Timeout for display form.
'Validate non-negative, type check will validate numeric.

    If sngNewTime >= 0# Then
        sngDisplayTime = sngNewTime
    Else
        Err.Raise 1001 + vbObjectError, Me.Name, INVAL_PROP
    End If
    
End Property
Property Page Form

The property page form, named frmFinalGVProperties, supports the display and entry of the Description, GVMonitor and DisplayTime properties and the read-only display of the Name property. It handles errors raised by the Property Let functions of the FinalGlobal class. The form hosts the following controls.

Name Type Use to
TxtDescription TextBox Display and enter the Description property.
TxtTimeout TextBox Display and enter the DisplayTime property.
TxtGVName TextBox Display and enter the GVMonitor property.
CancelButton CommandButton Close the form without saving the properties.
OKButton CommandButton Validate and save properties, then close the form.
LblTaskName Label Display the task name.

Adding the Property Page Form

This is the Visual Basic code for frmFinalGVProperties:

Private objTask         As DTS.Task
Private objFinalTask    As FinalGlobal

Const MSG_TITLE = "FinalGlobal Properties"

Public Property Set TaskObject(ByVal objNewTask As DTS.Task)
'When ref'ce to task updated, fetch custom task properties.
    Set objTask = objNewTask
    Set objFinalTask = objTask.CustomTask
    
    With objFinalTask
        lblTaskName.Caption = "Task name: " & vbCrLf & .Name
        txtDescription.Text = .Description
        txtGVName.Text = .GVMonitor
        txtTimeOut = (.DisplayTime)
    End With
    
End Property

Private Sub CancelButton_Click()
'On Cancel button, exit without updating properties.
    Unload Me
End Sub

Private Sub OKButton_Click()
'On OK button, validate and update properties.
    
    With objFinalTask
        On Error Resume Next
        .Description = txtDescription.Text
        If Err.Number <> 0 Then
            MsgBox "Description must be non-empty.", _
                    vbExclamation, MSG_TITLE
            Exit Sub
        End If
        
        On Error Resume Next
        .GVMonitor = txtGVName.Text
        If Err.Number <> 0 Then
            MsgBox "A global variable name must be entered.", _
                    vbExclamation, MSG_TITLE
            Exit Sub
        End If
        
        On Error Resume Next
        .DisplayTime = txtTimeOut.Text
        If Err.Number <> 0 Then
            MsgBox "Invalid timeout value """ & txtTimeOut.Text & """", _
                    vbExclamation, MSG_TITLE
            Exit Sub
        End If
        On Error GoTo 0
        
    End With
    
    Unload Me
    
End Sub
Runtime Display Form

The runtime display form, named frmFinalGlobal, supports the display of the global variable value and the implementation of the display timeout. The form caption displays the global variable name. frmFinalGlobal hosts the following controls.

Name Type Description
TxtMonitorValue TextBox Displays the global variable value.
TimDisplay Timer Implements the display timeout.

Adding the Runtime Display Form

This is the Visual Basic code for frmFinalGlobal:

Private blnUnloaded         As Boolean

Private Sub Form_Unload(Cancel As Integer)
'Turn off time and mark form unloaded for task.
    blnUnloaded = True
    timDisplay.Enabled = False
End Sub

Private Sub timDisplay_Timer()
'Timer has expired, unload the form.
    Unload Me
End Sub

Public Property Let MonitorValue(ByVal vntNewValue As Variant)
'Update global variable display
    txtMonitorValue.Text = CStr(vntNewValue)
    DoEvents
End Property

Public Property Let MonitorName(ByVal strNewName As String)
'Display name of global variable in form caption.
    Me.Caption = strNewName
    DoEvents
End Property

Public Property Get Unloaded() As Boolean
'Provide unloaded indication for task.
    Unloaded = blnUnloaded
End Property

Public Property Let DisplayTime(ByVal lngNewTime As Long)
'Set timeout for display form, start timer.
    timDisplay.Interval = lngNewTime
    timDisplay.Enabled = True
End Property

To build this DTS custom task

  1. In the Visual Basic development environment, create a new Microsoft ActiveX® DLL project.

  2. On the Project menu, click References, and under Available References, select the check box for Microsoft DTSPackage Object Library. Then, on the Project menu, click Properties and in the Project name box, change the project name from Project1 to something meaningful, like DTSSampleUI.

  3. Copy the code for the FinalGlobal class in the preceding code example to the class module in the Visual Basic project. Change the name of the class module from Class1 to FinalGlobal.

    If you use a different name, you need to change the references to FinalGlobal in the code to that name.

  4. Add a form to the project for the property page. Change its name to frmFinalGVProperties. If you use a different name, you will need to change the references to frmFinalGVProperties in the code to that name. Add three text boxes, two command buttons, and a label to the form. Name them as specified in the preceding table under Property Page Form. Label the buttons OK and Cancel. You may want to add additional labels to identify the text boxes and assign a meaningful caption. Copy the code for frmFinalGVProperties in the preceding code example to the code window for the form in the Visual Basic project.

  5. Add a form to the project for the runtime display. Change its name to frmFinalGlobal. If you use a different name, you will need to change the references to frmFinalGlobal in the code to that name. Add a text box and a timer control to the form. Name them as specified in the table above under Runtime Display Form. Copy the code for frmFinalGlobal in the preceding code example to the code window for the form in the Visual Basic project.

  6. If you want the task to have an icon other than the default icon, add a resource file to the project and add one or more icons to the resource file.

  7. On the File menu, click Make DTSSampleUI.dll to build the component. To register the task, open DTS Designer, and on the Task menu, click Register Custom Task and provide the information necessary to register the custom task.

See Also

CustomTaskUI Object

Edit Method

Help Method

New (CustomTaskUI) Method