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
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)
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 <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 Explicit Dim WithEvents oPackage As DTS.Package Public bCancel As BooleanNext, 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 isIf Dtspkg.dll is not found, SQL Server may not be installed correctly. See SQL Server Books Online for more information.
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:
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
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
Keywords: kbhowto kbhowtomaster KB221193
