BUG: DTS Package Execution Is Canceled Unexpectedly in a Visual Basic Application (319058)

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:

1. Create or use a DTS package that moves or copies data.
2. Create a Visual Basic Standard EXE project, and then set a reference to Microsoft DTSPackage Object Library.
3. Put a single command button on the default Form1.
4. 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
   					

5. 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:

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.

If pbCancel Then
				

If pbCancel = True Then