DTS Example: Running Concurrent Operations in Visual Basic

Custom tasks implemented in Microsoft® Visual Basic® must run on the package main thread because Visual Basic does not support free threading. Therefore, tasks implemented in Visual Basic and run in a Data Transformation Services (DTS) package in DTS Designer run sequentially, even when the package has no precedence constraints.

However, in a DTS package application, one task running on the main thread can run concurrently with others on worker threads. Thus, such a task could be implemented in Visual Basic.

DTS Designer also does not allow a task to display a modeless form or dialog box. However, in a DTS application modeless forms can be displayed. A modeless form is used in this example.

Concurrent Display

The following example code implements a DTS custom task that continuously displays the value of a global variable while other tasks are running. The custom task closes the display when the value of another global variable changes to TRUE.

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

Custom Task Class

In the custom task class, called ShowGlobal:

Properties GVMonitor and GVFinish specify the names of the global variable to be displayed and to the global variable that indicates completion, respectively. There is no property page user interface, as the application sets the properties directly.
The global variable display is updated continuously. The task raises the OnProgress and OnQueryCancel events every 3 seconds.
A log file string and a task record are written when task execution completes.
The DTS properties provider is explicitly invoked. The PersistPropertyBag interface is implemented.

Implementing the ShowGlobal Class

This is the Visual Basic code for the ShowGlobal class:

Implements DTS.CustomTask
Implements DTS.PersistPropertyBag

Const INVAL_PROP = "Invalid property value."

Private mstrDescription     As String   'Task/ShowAGlobal.Description property
Private mstrTaskName        As String   'Task/ShowAGlobal.Name property
Private mstrGVMonitorName   As String   'ShowAGlobal.GVMonitor property
Private mstrGVFinishName    As String   'ShowAGlobal.GVFinish property
Private frmShowGV           As frmFinalGlobal
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)
'Display value of global variable until another global indicates display finished.
    Dim objPackage      As DTS.Package2
    Dim objMonitor      As DTS.GlobalVariable
    Dim objFinished     As DTS.GlobalVariable
    Dim blnCancel       As Boolean
    Dim datCurrTime     As Date
    Dim datStartTime    As Date
    
    'Save reference to package, release parameter reference.
    Set objPackage = pPackage
    Set pPackage = Nothing
    pTaskResult = DTSTaskExecResult_Success
    
    'Initialize times for event generation.
    datStartTime = Now
    datCurrTime = Now
    
    'Get reference to global variables, exit if already finished.
    Set objMonitor = objPackage.GlobalVariables(mstrGVMonitorName)
    Set objFinished = objPackage.GlobalVariables(mstrGVFinishName)
    If objFinished.Value = True Then Exit Sub
    
    'Display form and use global variable name as title.
    Set frmShowGV = New frmFinalGlobal
    frmShowGV.MonitorName = mstrGVMonitorName
    frmShowGV.Show vbModeless
    
    'Refresh display until finished GV indicates done, user closes form, or app indicates Cancel.
    Do Until objFinished.Value Or frmShowGV.Unloaded
        frmShowGV.MonitorValue = objMonitor.Value
        
        'Every 3 sec, raise OnQueryCancel and OnProgress.
        If DateDiff("s", datCurrTime, Now) >= 3 Then
            datCurrTime = Now
            
            'Make sure package events object is valid.
            If Not pPackageEvents Is Nothing Then
            
                'Raise On Progress, OnQueryCancel, exit if response says to cancel.
                pPackageEvents.OnProgress Me.Description, "3 second notification", _
                        0, DateDiff("s", datStartTime, Now), 0
                pPackageEvents.OnQueryCancel Me.Description, blnCancel
                If blnCancel Then Exit Do
            End If
        End If
        
        DoEvents
    Loop
    
    'Write elapsed time and GV value to log, if log object valid.
    If Not pPackageLog Is Nothing Then
        pPackageLog.WriteStringToLog Me.Description & ": " & _
                    objMonitor.Name & " = " & objMonitor.Value
        pPackageLog.WriteTaskRecord 0, _
                    Me.Description & " elapsed time: " & _
                    (DateDiff("s", datStartTime, Now)) & " sec."
    End If
        
    'Close and release form.
    Unload frmShowGV
    Set frmShowGV = Nothing
        
End Sub

Private Property Get CustomTask_Properties() As DTS.Properties
'Use DTS properties provider to generate collection.
    Dim oPropsProvider As New DTS.PropertiesProvider
    
    Set CustomTask_Properties = oPropsProvider.GetPropertiesForObject(Me)
    Set oPropsProvider = Nothing

End Property

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

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

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

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

Private Sub PersistPropertyBag_Save(ByVal propBag As DTS.PropertyBag)
'Save property values in property bag.

    'On Error Resume Next
    propBag.Write "Name", mstrTaskName
    propBag.Write "Description", mstrDescription
    propBag.Write "GVMonitor", mstrGVMonitorName
    propBag.Write "GVFinish", mstrGVFinishName
    
End Sub

Private Sub PersistPropertyBag_Load(ByVal propBag As DTS.PropertyBag)
'Load property values from property bag.

    'On Error Resume Next.
    mstrTaskName = propBag.Read("Name")
    mstrDescription = propBag.Read("Description")
    mstrGVMonitorName = propBag.Read("GVMonitor")
    mstrGVFinishName = propBag.Read("GVFinish")
    
End Sub

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

Public Property Let GVMonitor(ByVal strNewName As String)
'Name of global variable to monitor, verify non-empty.
    
    If Len(strNewName) > 0 Then
        mstrGVMonitorName = strNewName
    Else
        Err.Raise 1001 + vbObjectError, Me.Name, INVAL_PROP
    End If
    
End Property

Public Property Get GVFinish() As String
'Name of global variable to indicate finish.
     GVFinish = mstrGVFinishName
End Property

Public Property Let GVFinish(ByVal strNewName As String)
'Name of global variable to indicate finish, verify non-empty.
    
    If Len(strNewName) > 0 Then
        mstrGVFinishName = strNewName
    Else
        Err.Raise 1001 + vbObjectError, Me.Name, INVAL_PROP
    End If
    
End Property

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

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

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

Public Property Let Description(ByVal strNewDescr As String)
'Implements FinalGlobal.Description
    mstrDescription = strNewDescr
End Property

DTS Package Application

This DTS application uses the ShowGlobal custom task to display the number of rows copied, via the Rows Copied global variable, while another step copies rows from a table in one database to a table in another. The copy step closes the display by setting the Copy Complete global variable to TRUE.

The copy step uses a DataPumpTask2 object with a DataPumpTransformScript transformation to copy certain columns from the Products table in the Microsoft SQL Server™ Northwind database to a table named NorthwindProducts in a database named DTS_UE. DTS lookups are used to replace the CategoryID field with the CategoryName from the Northwind Categories table, and to replace the SupplierID field with the CompanyName from the Northwind Suppliers table.

Creating the DTS Package Application

Important This sample application is intentionally implemented to run slowly so the display will be more convenient to view. It uses the same connection for the data source and both lookups, and uses 0 for the MaxCacheRows property for both lookups. In an actual application that copies and transforms databases, you should use a separate connection for lookups, or for each lookup. You should also use a nonzero value for MaxCacheRows. The default of 100 is recommended for the initial choice.