Error: "Execution was cancelled by user" when running DTS

[datras]

I keep getting the error: “Execution was cancelled by user” when running my DTS package from VBA or VB. This has only been a problem on my PC and not on the person who use the application for import of data. Now she has got her PC upgraded and now she has the problem as well.


The DTS package imports data to a database and calls an other package.

At a Microsoft support site (

http://support.microsoft.com/default.aspx?scid=kb;en-us;319058

) I got the explanation that it was a problem in the OnQueryCancel event and they provided some code to work around the problem (see below). However, when it gets to the sub; pkg_OnProgress, VB close with an error or it just keeps hanging.

An other place I read that it was not possible to use the VB event handler together with a parent and child DTS package?

Now, does this mean that I can’t use DTS package all together?!!
I hope some one has found the solution on this problem and can help me out.


Code:

Option Explicit
Dim WithEvents pkg As DTS.Package

Private Sub Command1_Click()

Dim stp As DTS.Step
Set pkg = New DTS.Package

Dim strFilename As String
Dim strFileLoc As String
Dim strSurvey As String
Dim strUser As String
Dim dtsChdPkg As DTS.Package2
Dim strError As String

'get txt input from form
strSurvey = "name"
strFileLoc = "parth"
strFilename = "filename"
strUser = "lena"

'initialise child package
Set dtsChdPkg = New Package2
'load Child package
dtsChdPkg.LoadFromSQLServer "server", "user", "password", DTSSQLStgFlag_Default, , "{1437429E-79AA-480C-A32E-B985507FF217}"

'set global variables
dtsChdPkg.GlobalVariables.Item("Survey").Value = strSurvey
dtsChdPkg.GlobalVariables.Item("User").Value = strUser
dtsChdPkg.GlobalVariables.Item("Filename").Value = strFilename
dtsChdPkg.GlobalVariables.Item("Filelocation").Value = strFileLoc
'save child package
dtsChdPkg.SaveToSQLServer "server", "user", "password"
'release child DTS object
dtsChdPkg.UnInitialize

'Do Dts Package Stuff
Set pkg = New Package
pkg.LoadFromSQLServer "server", "user", "password",
DTSSQLStgFlag_Default, , "{5D342938-D590-43CA-AF4E-1500EEFF3614}"

'set global variables
pkg.GlobalVariables.Item("Survey").Value = strSurvey
pkg.GlobalVariables.Item("User").Value = strUser
pkg.GlobalVariables.Item("Filename").Value = strFilename
pkg.GlobalVariables.Item("Filelocation").Value = strFileLoc

'save to server
pkg.SaveToSQLServer "server", "user", "password"

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