Knowledge Base

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

Article ID: 319048

Article Last Modified on 11/14/2003

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft Visual Basic 6.0 Professional Edition
Microsoft Visual Basic 6.0 Enterprise Edition

This article was previously published under Q319048

SYMPTOMS

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:

(1:Child Package) SubStep '<title of step> Step' failed with the following error:
Need to run the object to perform this operation
(Microsoft Data Transformation Services (DTS) Package (80040005):
Provider generated code execution exception: EXCEPTION_ACCESS_VIOLATION)

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.

CAUSE

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.

WORKAROUND

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.

MORE INFORMATION

Steps to Reproduce Behavior

To reproduce the behavior, follow these steps:

Open the SQL Server 2000 Enterprise Manager and create a new, empty database.
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.
Save the package to SQL Server with the name "Child Package", and then close the wizard.
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."
Start Visual Basic 6.0 and create a new Standard EXE Project.
Add a reference to the Microsoft DTSPackage Object Library.

Insert the following declaration at form level:

Option Explicit
Dim WithEvents pkg As DTS.Package

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

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

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:

221193 HOW TO: Install Data Transformation Services (DTS) Event Handlers in Visual Basic

240221 INF: How To Handle Errors in DTS Package and Step Objects

271889 PRB: Error Message: "Exception Access Violation 2147221499.Need to run the object to perform this operation" Occurs When You Run a DTS Package in Microsoft Visual Basic Code

242391 INF: DTS Package Development, Deployment, and Performance

Keywords: kbdatabase kbprb KB319048

Microsoft KB Archive/319048

Contents