Microsoft KB Archive/221193

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

Article ID: 221193

Article Last Modified on 5/1/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 64-bit Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q221193



IN THIS TASK
SUMMARY
 * Visual Basic declaration statements for the DTS Event Handlers
 * Using the SQL Server DTS Event Handlers in Visual Basic
 * Implement the five Event Handler declarations
 * Loading and executing a DTS package from an existing file
 * Run the application in Visual Basic

REFERENCES



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 _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 _OnFinish(ByVal EventSource As String)

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

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

Private Sub _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.

 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.  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 _OnError, _OnStart, _OnFinish, _OnProgress, and _OnQueryCancel, and replaces 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 \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 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

