Knowledge Base

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:="(local)", _
        Flags:=DTSSQLStgFlag_UseTrustedConnection, _
        PackageName:="Test Package"
    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 "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)
    Static cnt As Integer
    Debug.Print "Step " & 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 " Resetting value of pbCancel flag, count = " & 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.

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:

Open a Command window.
At the command prompt, type dtsrun.exe /?
Press ENTER. Information about the utility will appear on the screen.

STATUS

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

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 "Execution was canceled by user" 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

REFERENCES

For additional information about the use of DTS event handlers in Visual Basic, 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: kbbug kbdatabase kbprogramming kbpending KB319058

Microsoft KB Archive/319058

Contents