PRB: Cannot Use Data Transformation Services Event Handlers in Visual Basic with Execute Package Task (319048)

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the SQL Server 2000 Enterprise Manager and create a new, empty database.
2. Start the DTS Export Wizard and copy all the tables and rows in the Northwind sample database to the empty database you created in step 1.
3. Save the package to SQL Server with the name "Child Package", and then close the wizard.
4. Create a new package in the DTS Designer, and then add a single Execute Package task that calls the child package. Save the new package with the name "Parent Package."
5. Start Visual Basic 6.0 and create a new Standard EXE Project.
6. Add a reference to the Microsoft DTSPackage Object Library.
7. Insert the following declaration at form level:

   Option Explicit
   Dim WithEvents pkg As DTS.Package

8. Add procedure stubs for the five (5) DTS package events as in the following example. Each Sub and End Sub block must contain at least a comment, or the Sub and End Sub block is removed:

   Private Sub pkg_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, pbCancel As Boolean)
       Debug.Print "An error occurred." & vbCrLf & _
           "Event source: " & EventSource & vbCrLf & _
           "Error code: " & ErrorCode & vbCrLf & _
           "Source: " & Source & vbCrLf & _
           "Description: " & Description
   End Sub
   
   Private Sub pkg_OnFinish(ByVal EventSource As String)
       'stub
   End Sub
   
   Private Sub pkg_OnProgress(ByVal EventSource As String, ByVal ProgressDescription As String, ByVal PercentComplete As Long, ByVal ProgressCountLow As Long, ByVal ProgressCountHigh As Long)
       'stub
   End Sub
   
   Private Sub pkg_OnQueryCancel(ByVal EventSource As String, pbCancel As Boolean)
       'stub
   End Sub
   
   Private Sub pkg_OnStart(ByVal EventSource As String)
       'stub
   End Sub

9. Place a single command button on the default Form1, and then insert the following code in the command button Click event. Adjust the SQL Server name if necessary.

       Dim stp As DTS.Step
       Set pkg = New DTS.Package
       pkg.LoadFromSQLServer ServerName:="(local)", _
           Flags:=DTSSQLStgFlag_UseTrustedConnection, _
           PackageName:="Parent Package"
       For Each stp In pkg.Steps
           stp.ExecuteInMainThread = True
       Next
       pkg.FailOnError = True
       pkg.Execute
       Set pkg = Nothing

10. Run the project. In the Immediate Window, the OnError event handler prints error information similar to the message shown in the "Symptoms" section of this article. If you step through the code, you will see different behavior. The application stops responding when it enters the OnProgress event, and does not report an error. You have to use the Task Manager to end the unresponsive process.

REFERENCES