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:
back to the top Using the SQL Server DTS Event Handlers in Visual Basic
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)
Here is an example that shows you how to use the SQL Server DTS Event Handlers in Visual Basic.
back to the top IImplement the five Event Handler declarations
- 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 <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:
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.
Option ExplicitDim WithEvents oPackage As DTS.PackagePublic bCancel As Boolean
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:
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.
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
: 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
, 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:
back to the top Run the application in Visual Basic
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
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.
back to the top
RESULTS==============================oPackage_OnStart FiredoPackage_OnProgress FiredoPackage_OnQueryCancel FiredoPackage_OnProgress FiredoPackage_OnQueryCancel Fired...--omitted for brevity...oPackage_OnProgress FiredoPackage_OnQueryCancel FiredoPackage_OnFinish FiredoPackage_OnQueryCancel Fired