INF: How To Install DTS Event Handlers In Visual Basic

ID: Q221193

The information in this article applies to:

Microsoft SQL Server version 7.0

SUMMARY

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

MORE INFORMATION

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)

The following steps are an example of utilizing the SQL Server DTS Event Handlers in Visual Basic.

Create or find a DTS package. 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 an error occurs with this sample. This happens because a DTS package must be identified by a unique name or guid number.

Using Visual Basic create a new Project of type Standard.EXE. Form1 is created by default. Add a new CommandButton to Form1. The default button name is Command1. Double-click the form to open it and the code window appears.

This example defines an object of type DTS.Package, called oPackage. Therefore, the example takes on the following DTS Event Handler declarations of <object>_OnError, <object>_OnFinish, <object>_OnFinish, <object>_OnProgress and <object>_OnQueryCancel and replacing <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 allows you to create a DTS.Package. From the Tools menu, choose References and select Microsoft DTSPackage Object Library.

NOTE: If you do not have a reference for the Microsoft DTSPackage Object Library, use Explorer to locate the file Dtspkg.dll. Dtspkg.dll is typically located in the <MSSQL7>\binn directory. Once you locate the file, you can manually register the file using Regsvr32.exe. The following example shows how to run this utility from a command prompt in the same directory as the Dtspkg.dll file:

REGSVR32 dtspkg

The message returned should be "DLLRegisterServer in dtspkg completed". If this file is not found then the SQL Server may not be installed correctly. Check the SQL Server books Online for further information.

The following code is the implementation of 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.

Place the following code in Form1 below the declaration statements that were shown at the beginning of the MORE INFORMATION section.

NOTE: The preceding five Event Handlers must all be declared and implemented, or Visual Basic behavior can become inconsistent.


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 be terminated. 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

Go back to Form1, double-click the CommandButton, and then paste in the following code. This code sample demonstrates loading and executing 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

Run the application in Visual Basic. Click Command1 to execute the DTS transformation. Observe in the Immediate Window the execution of the following events. The amount of these messages will be affected by the amount of steps or operations within 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

Additional query words:


Keywords          : SSrvProg SSrvVisB kbSQLServ700 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: May 25, 1999