INF: How To Install DTS Event Handlers In Visual Basic

• 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) 

1. 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.



2. 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.



3. 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 




4. 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 




5. 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