Microsoft KB Archive/319048

= 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 ' 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.



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 &quot;Child Package&quot;, 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 &quot;Parent Package.&quot; Start Visual Basic 6.0 and create a new Standard EXE Project. Add a reference to the Microsoft DTSPackage Object Library.</li>  Insert the following declaration at form level: Option Explicit Dim WithEvents pkg As DTS.Package </li>  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 &quot;An error occurred.&quot; & vbCrLf & _ &quot;Event source: &quot; & EventSource & vbCrLf & _ &quot;Error code: &quot; & ErrorCode & vbCrLf & _ &quot;Source: &quot; & Source & vbCrLf & _ &quot;Description: &quot; & 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 </li>  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:=&quot;(local)&quot;, _ Flags:=DTSSQLStgFlag_UseTrustedConnection, _ PackageName:=&quot;Parent Package&quot; For Each stp In pkg.Steps stp.ExecuteInMainThread = True Next pkg.FailOnError = True pkg.Execute Set pkg = Nothing </li> Run the project. In the Immediate Window, the OnError event handler prints error information similar to the message shown in the &quot;Symptoms&quot; 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.</li></ol>