DTS Example: Running Concurrent Operations in Visual Basic

DTS Programming

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

To build this DTS custom task

In the Visual Basic development environment, create a new ActiveX DLL project.
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 DTSConcurrentSample.
Copy the code for the ShowGlobal 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 ShowGlobal. If you use a different name, you need to change the references to ShowGlobal in the code to that name.
Add the frmFinalGlobal form.
For more information about this form, see DTS Example: Including a User Interface in Visual Basic.
On the File menu, click Make DTSConcurrentSample.dll to build the component.
Do not register the task in DTS Designer.

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

This is the definition of the NorthwindProducts table in DTS_UE:

CREATE TABLE [DTS_UE].[dbo].[NorthwindProducts] (
   [ProductName] [nvarchar] (40) NULL ,
   [CategoryName] [nvarchar] (25) NULL ,
   [CompanyName] [nvarchar] (40) NULL )

This is the Visual Basic code for the application:

Public Sub Main()
'Copy Northwind..Products names, categories, suppliers to DTS_UE..NorthwindProducts.
    Dim objPackage    As DTS.Package2
    Dim objConnect    As DTS.Connection2
    Dim objStep       As DTS.Step2
    Dim objTask       As DTS.Task
    Dim objPumpTask   As DTS.DataPumpTask2
    Dim objCustTask   As DTSConcurrentSample.ShowGlobal
    Dim objTransform  As DTS.Transformation2
    Dim objLookUp     As DTS.Lookup
    Dim objTranScript As DTSPump.DTSTransformScriptProperties2
    Dim sVBS          As String       'VBScript text

    Set objPackage = New DTS.Package
    objPackage.FailOnError = True
    objPackage.LogFileName = "C:\Temp\TestConcurrent.Log"
    
    'Establish connections to data source and destination.
    Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
    With objConnect
        .ID = 1
        .DataSource = "(local)"
        .UseTrustedConnection = True
    End With
    objPackage.Connections.Add objConnect
    Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
    With objConnect
        .ID = 2
        .DataSource = "(local)"
        .UseTrustedConnection = True
    End With
    objPackage.Connections.Add objConnect

    'Create copy step and task, link step to task.
    Set objStep = objPackage.Steps.New
    objStep.Name = "NorthwindProductsStep"
    Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
    Set objPumpTask = objTask.CustomTask
    objPumpTask.Name = "NorthwindProductsTask"
    objStep.TaskName = objPumpTask.Name
    objStep.ExecuteInMainThread = False
    objPackage.Steps.Add objStep
    
    'Link copy task to connections.
    With objPumpTask
        .SourceConnectionID = 1
        .SourceSQLStatement = _
            "SELECT ProductName, CategoryID, SupplierID " & _
            "FROM Northwind..Products"
        .DestinationConnectionID = 2
        .DestinationObjectName = "[DTS_UE].[dbo].[NorthwindProducts]"
        .UseFastLoad = False
        .MaximumErrorCount = 99
    End With
    
    'Create lookups for supplier and category.
    Set objLookUp = objPumpTask.Lookups.New("CategoryLU")
    With objLookUp
        .ConnectionID = 1
        .Query = "SELECT CategoryName FROM Northwind..Categories " & _
                "WHERE CategoryID = ? "
        .MaxCacheRows = 0
    End With
    objPumpTask.Lookups.Add objLookUp
    Set objLookUp = objPumpTask.Lookups.New("SupplierLU")
    With objLookUp
        .ConnectionID = 1
        .Query = "SELECT CompanyName FROM Northwind..Suppliers " & _
                "WHERE SupplierID = ? "
        .MaxCacheRows = 0
    End With
    objPumpTask.Lookups.Add objLookUp
    
    'Create and initialize rowcount and completion global variables.
    objPackage.GlobalVariables.AddGlobalVariable "Copy Complete", False
    objPackage.GlobalVariables.AddGlobalVariable "Rows Copied", 0
    objPackage.ExplicitGlobalVariables = True
    
    'Create transform to copy row, signal completion.
    Set objTransform = objPumpTask.Transformations. _
            New("DTSPump.DataPumpTransformScript")
    With objTransform
        .Name = "CopyNorthwindProducts"
        .TransformPhases = DTSTransformPhase_Transform + _
                        DTSTransformPhase_OnPumpComplete
        Set objTranScript = .TransformServer
    End With
    With objTranScript
        .FunctionEntry = "CopyColumns"
        .PumpCompleteFunctionEntry = "PumpComplete"
        .Language = "VBScript"
        sVBS = "Option Explicit" & vbCrLf
        sVBS = sVBS & "Function CopyColumns()" & vbCrLf
        sVBS = sVBS & "   DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
        sVBS = sVBS & "   DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
        sVBS = sVBS & "   DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " & vbCrLf
        sVBS = sVBS & "   DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
        sVBS = sVBS & "   CopyColumns = DTSTransformStat_OK" & vbCrLf
        sVBS = sVBS & "End Function" & vbCrLf
        
        sVBS = sVBS & "Function PumpComplete()" & vbCrLf
        sVBS = sVBS & "   DTSGlobalVariables(""Copy Complete"") = True" & vbCrLf
        sVBS = sVBS & "   PumpComplete = DTSTransformStat_OK" & vbCrLf
        sVBS = sVBS & "End Function" & vbCrLf
        
        .Text = sVBS
    End With
    objPumpTask.Transformations.Add objTransform
    objPackage.Tasks.Add objTask
    
    'Create monitor step and task, link step to task.
    Set objStep = objPackage.Steps.New
    objStep.Name = "GVMonitorStep"
    Set objTask = objPackage.Tasks.New("DTSConcurrentSample.ShowGlobal")
    objTask.Name = "GVMonitorTask"
    objStep.TaskName = objTask.Name
    Set objCustTask = objTask.CustomTask
    objCustTask.GVMonitor = "Rows Copied"
    objCustTask.GVFinish = "Copy Complete"
    objStep.ExecuteInMainThread = True
    objPackage.Steps.Add objStep
    
    'Link monitor task to package, run package.
    objPackage.Tasks.Add objTask
    objPackage.Execute
   
End Sub

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.

To build this DTS application

Build the DTSConcurrentSample.ShowGlobal custom task, as described in the preceding example.
Create a database named DTS_UE containing a table named NorthwindProducts, using the definition given above.
Create a Standard EXE project in the Visual Basic development environment.
On the Project menu, click References, and under Available References, select the check boxes for DTSConcurrentSample, Microsoft DTSDataPump Scripting Object Library and Microsoft DTSPackage Object Library.
Add a standard module to the project, and then copy the code for the application in the preceding code example into the module. Remove the form Form1 from the project.
On the Run menu, click Start.
Observe the displayed rowcount.

DTS Example: Running Concurrent Operations in Visual Basic

DTS Programming