Microsoft KB Archive/319058

= BUG: DTS Package Execution Is Canceled Unexpectedly in a Visual Basic Application =

Article ID: 319058

Article Last Modified on 4/6/2004

-

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 Q319058



BUG #: 356809 (SHILOH_BUGS)

BUG #: 1289 (DSDAG)



SYMPTOMS
When you execute a multiple-step Data Transformation Services (DTS) package from a Visual Basic application in which the DTS event handlers are installed, the package execution may be canceled unexpectedly and you may receive the following error message:

Runtime error '-2147220441 (80040427)': Execution was canceled by user.



CAUSE
This problem occurs because the pbCancel Boolean flag in the OnQueryCancel event, without user intervention, is sometimes set to True unexpectedly, which causes package execution to be canceled prematurely.



WORKAROUND
To work around this problem, you can use either of the following methods, based on your requirements.

Method 1
Add code to the OnQueryCancel event to test the value of the pbCancel flag and change its value back from True to False in cases where the user does not request cancellation:  Create or use a DTS package that moves or copies data. Create a Visual Basic Standard EXE project, and then set a reference to Microsoft DTSPackage Object Library. Put a single command button on the default Form1.  Paste the following code into the code module of Form1: (Adjust SQL Server and the DTS package names as necessary.) Option Explicit Dim WithEvents pkg As DTS.Package

Private Sub Command1_Click Dim stp As DTS.Step Set pkg = New DTS.Package pkg.LoadFromSQLServer ServerName:=&quot;(local)&quot;, _ Flags:=DTSSQLStgFlag_UseTrustedConnection, _ PackageName:=&quot;Test Package&quot; For Each stp In pkg.Steps stp.ExecuteInMainThread = True Next pkg.FailOnError = True pkg.Execute Set pkg = Nothing End Sub

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) Static cnt As Integer Debug.Print &quot;Step &quot; & cnt cnt = cnt + 1 End Sub

Private Sub pkg_OnQueryCancel(ByVal EventSource As String, pbCancel As Boolean) Static cnt As Integer If pbCancel Then cnt = cnt + 1 Debug.Print &quot; Resetting value of pbCancel flag, count = &quot; & cnt pbCancel = False End If End Sub

Private Sub pkg_OnStart(ByVal EventSource As String) 'stub End Sub  Run the project. In the Immediate (Debug) window, you see that the pbCancel flag has changed from True back to False several times so that the execution of the package can finish. During testing with a 15-step package, this change occurs between two and four times.</ol>

Method 2
Manually execute the package by using the dtsrun utility (Dtsrun.exe). You can use the Shell function to execute Dtsrun.exe. For information about how to use Dtsrun.exe to execute your package, follow these steps:
 * 1) Open a Command window.
 * 2) At the command prompt, type dtsrun.exe /?
 * 3) Press ENTER. Information about the utility will appear on the screen.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

MORE INFORMATION
To reproduce the problem, comment out the code in the OnQueryCancel event in the sample code, and then run the project again. You see the &quot;Execution was canceled by user&quot; error message.

The test to determine whether the pbCancel flag has been set to True must be coded as follows: If pbCancel Then The following syntax will fail: If pbCancel = True Then