How to install Data Transformation Services (DTS) Event Handlers in Visual Basic (221193)

SUMMARY

The Data Transformation Services (DTS) events are documented in SQL Server Books Online for the package object; however, the documentation does not include the declarations for Microsoft Visual Basic to the events. This article contains a code sample that includes the declarations for the Visual Basic events.

back to the top

Visual Basic declaration statements for the DTS Event Handlers

The following example shows the Visual Basic declaration statements for the DTS Event Handlers:

Private Sub <object>_OnError(ByVal EventSource As String, _
                                ByVal ErrorCode As Long, _
                                ByVal Source As String, _
                                ByVal Description As String, _
                                ByVal HelpFile As String, _
                                ByVal HelpContext As Long, _
                                ByVal IDofInterfaceWithError As String, _
                                ByRef pbCancel As Boolean)

Private Sub <object>_OnFinish(ByVal EventSource As String)

Private Sub <object>_OnProgress(ByVal EventSource As String, _
                                ByVal ProgressDescription As String, _
                                ByVal PercentComplete As Long, _
                                ByVal ProgressCountLow As Long, _
                                ByVal ProgressCountHigh As Long)

Private Sub <object>_OnQueryCancel(ByVal EventSource As String, _
                                    ByRef pbCancel As Boolean)

Private Sub <object>_OnStart(ByVal EventSource As String)
				

back to the top

Using the SQL Server DTS Event Handlers in Visual Basic

Here is an example that shows you how to use the SQL Server DTS Event Handlers in Visual Basic.

1. Create or find a DTS package, and then save it to a file. For example, C:\Test.dts.
   
   NOTE: Make sure that the file only includes one DTS package. If there are multiple DTS packages, you receive an error with this sample. You receive an error because a DTS package must be identified by a unique name or GUID number.
2. Use Visual Basic and create a new Project of type Standard.exe. By default, a form named Form1 is created. Add a new command button to Form1. The default button name is Command1. Double-click the form to open it. The code window appears.
   
   This example defines an object of type DTS.Package named oPackage. Therefore, the example takes on the following DTS Event Handler declarations of <object>_OnError, <object>_OnStart, <object>_OnFinish, <object>_OnProgress, and <object>_OnQueryCancel, and replaces <object> with the declared object name of "oPackage."
   
   In the General and Declarations code sections, add the following declarations for the event handlers:

   Option Explicit
   
   Dim WithEvents oPackage As DTS.Package
   Public bCancel As Boolean
   
   						

   Next, add the type library reference for DTS objects that permit you to create a DTS.Package. On the Project menu, click References. Click to select Microsoft DTSPackage Object Library.
   
   NOTE: If you do not have a reference for the Microsoft DTSPackage Object Library, use Explorer to locate the Dtspkg.dll file. Dtspkg.dll is typically located in the <MSSQL7>\Binn folder. After you locate the file, you can manually register the Dtspkg.dll file by using Regsvr32.exe. The following example shows how you can run this utility from a command prompt in the same folder as the Dtspkg.dll file:
   
   REGSVR32 dtspkg
   
   The message you receive is
   DLLRegisterServer in dtspkg completed.
   If Dtspkg.dll is not found, SQL Server may not be installed correctly. See SQL Server Books Online for more information.

back to the top

IImplement the five Event Handler declarations

Use the following code to implement the five Event Handler declarations. In Visual Basic, change all the steps to only run on the main thread to prevent any synchronization problems. See the following example, which occurs when the application is being debugged.

IMPORTANT: The earlier five Event Handlers must all be declared and implemented. Otherwise, the Visual Basic application quits abnormally (without executing the DTS package), and you recevie this error message: Copy the following code to Form1. Paste it under the declaration statements that were shown at the beginning of the "More Information" section:

Public Sub ChangeAllStepsToRunOnMainThread(oPkg As DTS.Package)
    Dim nStepCount As Integer
    For nStepCount = 1 To oPkg.Steps.Count
        oPkg.Steps(nStepCount).ExecuteInMainThread = True
    Next nStepCount
End Sub

Private Sub oPackage_OnError(ByVal EventSource As String, _
                                ByVal ErrorCode As Long, _
                                ByVal Source As String, _
                                ByVal Description As String, _
                                ByVal HelpFile As String, _
                                ByVal HelpContext As Long, _
                                ByVal IDofInterfaceWithError As String, _
                                ByRef pbCancel As Boolean)
    Debug.Print "oPackage_OnError Fired"
End Sub

Private Sub oPackage_OnFinish(ByVal EventSource As String)
    Debug.Print "oPackage_OnFinish Fired"
End Sub

Private Sub oPackage_OnProgress(ByVal EventSource As String, _
                                ByVal ProgressDescription As String, _
                                ByVal PercentComplete As Long, _
                                ByVal ProgressCountLow As Long, _
                                ByVal ProgressCountHigh As Long)
    ' The DTS Package will trigger this event at certain intervals
    ' to report the progress of the package. This can be controlled
    ' by setting the DTS.Package.ProgressRowCount property.
    Debug.Print "oPackage_OnProgress Fired"
End Sub

Private Sub oPackage_OnQueryCancel(ByVal EventSource As String, _
                                    ByRef pbCancel As Boolean)
    ' The DTS package will trigger this event at certain intervals to check
    ' whether the execution of the package should quit. Set
    ' pbCancel to true to cancel the execution of the package.
    Debug.Print "oPackage_OnQueryCancel Fired"
    If bCancel Then
        pbCancel = True
        Debug.Print "Canceling package execution."
    Else
        pbCancel = False
    End If
End Sub

Private Sub oPackage_OnStart(ByVal EventSource As String)
    Debug.Print "oPackage_OnStart Fired"
End Sub
				

NOTE: If you change the default value for any variable, make sure that you reset the default value after each Data Pump task. Otherwise, the IDTSDataPumpTransform::OnTransformComplete task resets the values to their defaults, and the package execution might fail.

back to the top

Loading and executing a DTS package from an existing file

In Form1, double-click Command1, and then paste in the following code. This code sample demonstrates how to load and execute a DTS package from an existing file:

Private Sub Command1_Click()
    Dim oTask As DTS.DataPumpTask
    
    Dim strFileName As String
    Dim strFilePassword As String
    
    bCancel = False
    
    Set oPackage = New DTS.Package
    
    strFileName = "C:\Temp\Test.dts"
    'strFilePassword = "VerySecurePassword" 'Use if file has password
    
    oPackage.LoadFromStorageFile strFileName, strFilePassword
    'For x = 1 To oPackage.Tasks.Count
    '    Debug.Print oPackage.Tasks.Item(x)
    'Next x
    
    ' Only call the following when developing the application. You
    ' can comment out the call when you build your application.
    ChangeAllStepsToRunOnMainThread oPackage
    
    Set oTask = oPackage.Tasks.Item("DTSTask_DTSDataPumpTask_1").CustomTask
    
    oTask.ProgressRowCount = 1
    
    Screen.MousePointer = vbHourglass
    
    oPackage.Execute
    
    Screen.MousePointer = vbNormal
    
    Set oTask = Nothing
    Set oPackage = Nothing
End Sub
				

back to the top

Run the application in Visual Basic

Click Command1 to execute the DTS transformation. In the Immediate Window, watch the execution of the following events. The number of messages is affected by the number of steps or operations in the DTS package.

RESULTS
==============================
oPackage_OnStart Fired
oPackage_OnProgress Fired
oPackage_OnQueryCancel Fired
oPackage_OnProgress Fired
oPackage_OnQueryCancel Fired
...
--omitted for brevity
...
oPackage_OnProgress Fired
oPackage_OnQueryCancel Fired
oPackage_OnFinish Fired
oPackage_OnQueryCancel Fired
				

back to the top