You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q221193
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 ExplicitDim WithEvents oPackage As DTS.PackagePublic 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:
Runtime error -21477221499 (80040005)Provider generated code execution exception:EXCEPTION_ACCESS_VIOLATION
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 nStepCountEnd SubPrivate 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 SubPrivate Sub oPackage_OnFinish(ByVal EventSource As String)    Debug.Print "oPackage_OnFinish Fired"End SubPrivate 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 SubPrivate 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 IfEnd SubPrivate 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 = NothingEnd 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 FiredoPackage_OnProgress FiredoPackage_OnQueryCancel FiredoPackage_OnProgress FiredoPackage_OnQueryCancel Fired...--omitted for brevity...oPackage_OnProgress FiredoPackage_OnQueryCancel FiredoPackage_OnFinish FiredoPackage_OnQueryCancel Fired				
back to the top

REFERENCES
back to the top

Properties

Article ID: 221193 - Last Review: 05/01/2006 19:51:46 - Revision: 4.5

  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • kbhowto kbhowtomaster KB221193
Feedback