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

If you install Data Transformation Services (DTS) Package event handlers in a Visual Basic application, and the package you call uses an Execute Package task to call another package, an Exception Access Violation (AV) similar to the following may occur: The Access Violation occurs when these conditions are true:

• You use DTS package event handlers in the Visual Basic application.
• You use a parent DTS package that uses an Execute Package task to call a child DTS package.

Threading limitations in the design of Visual Basic 6.0 prevent the nested child package from reporting it's events up through the parent package's event handler.

To work around the problem, you can use one or both of these methods:

• Remove the DTS Package event handlers from the Visual Basic application.
  
  -or-
  
  
• Restructure your DTS Packages and Tasks and do not use parent DTS packages that use the Execute Package task to call child DTS packages.

Steps to Reproduce Behavior

To reproduce the behavior, follow these steps:

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

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: