Export (0) Print
Expand All

DTS Example: Including a User Interface in Visual Basic

SQL Server 2000

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:

  • A GVMonitor property, which specifies the name of the global variable to be displayed.

  • A DisplayTime property, which specifies the time after which the display is closed.

  • Description and Name properties that tie CustomTask interface properties to the FinalGlobal class.

    It is acceptable to use Name because the property page exposes Name as read-only. Thus, the user cannot cause an error by attempting to change it.

  • A property page that is displayed when the CustomTaskUI New or Edit methods are invoked. These methods are called by DTS Designer when you either drag the task icon to the design sheet or right-click the icon and select Properties.

  • A Help page that is displayed when the CustomTaskUI Help method is invoked.
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
Show:
© 2014 Microsoft