Hi

I'm trying to test some DTS packages, and ran into the familiar problem
that the package itself doesn't accurately report errors in the steps -
beyond "the step failed".
Tried running the package in the DTS Designer, tried running it by
knocking together a VB utility (using the DTS.Package2.Execute method,
with FailOnError=True, then calling GetExecutionErrorInfo for each step
in the package) - even tried running WithEvents and using the package
OnError event handler, with all the hideous problems that involves.
None of these give me any information of any use about errors - and I'm
sure I managed to get useful error information at some point in the
past (in a previous job, on some other company's server).

The result is always the same: all I get is the moronically useless
message "ActiveX Scripting encountered a Run Time error during the
execution of the script". I'm sure this didn't use to happen - I know
how to use GetExecutionErrorInfo for a reason! I used to get sensible
error messages out of ActiveX scripts in DTS packages.
Is there some basic version issue/switch/setting issue I'm missing?
I'm running SQL2K client tools talkign to a SQL2K Developer Edition
server.

thanks for any help


Seb Thirlway

Hi Seb,
I was wondering if you can send me sample code on how to call
GetExecutionErrorInfo from within an ActiveX script. I call the the package
successfully but when I try to retrieve the error message, I get Type
missmatch or invalid number of call argument.

Here is my sample code:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Dim ADOconn
Dim ADOrst
Dim oPackage
Dim oStep
Dim iErrorCode
Dim iCount
Dim sTaskName
Dim sSource
Dim sErrorDesc
Dim sHelpFileName
Dim iHelpContextID
Dim sInterfaceID
Dim sErrorMessage
Dim sErrorNumConv
Dim sProcessCategory
Dim sProcessName

Set oPackage = CreateObject("DTS.Package")

sProcessName = "3002PCKG"

oPackage.LoadFromSQLServer "(local)","","","256",,,,sProcessName
oPackage.GlobalVariables.Item("giJobID").Value =
DTSGlobalVariables("giJobID").Value
oPackage.GlobalVariables.Item("gsAS400SourceUDL"). Value =
DTSGlobalVariables("gsAS400SourceUDL").Value
oPackage.GlobalVariables.Item("gsSourceUDL").Value =
DTSGlobalVariables("gsSourceUDL").Value
oPackage.GlobalVariables.Item("gsTargetUDL").Value =
DTSGlobalVariables("gsTargetUDL").Value

oPackage.Execute

'************************************************* *************
' GET STATUS AND ERROR
'************************************************* *************

'LOOK FOR STEPS THAT COMPLETED AND FAILED.
For iCount = 1 To oPackage.Steps.Count

If oPackage.Steps(iCount).ExecutionStatus =
DTSStepExecStat_Completed Then

If oPackage.Steps(iCount).ExecutionResult =
DTSStepExecResult_Failure Then


'************************************************* *************
'GET THE STEP ERROR INFORMATION
'************************************************* *************

iErrorCode = CLng(-1)
iHelpContextID = CLng(-1)

oPackage.Steps(iCount).GetExecutionErrorInfo iErrorCode, sSource,
sErrorDesc, sHelpFileName, iHelpContextID, sInterfaceID

If iErrorCode < 65536 And iErrorCode > -65536 Then

sErrorNumConv = "x" & Hex(iErrorCode) & ", " &
CStr(iErrorCode)

Else

sErrorNumConv = "x" & Hex(iErrorCode) & ", x" & _
Hex(iErrorCode And -65536) & " + " & CStr(iErrorCode
And 65535)

End If

sMessage = sMessage & vbCrLf & _
"Step " & oPackage.Steps.Name & " failed, error:
" & _
sErrorNumConv & vbCrLf & sDescr & vbCrLf
End If
End If
Next

Msgbox sMessage

oPackage.Uninitialize()
Set oPackage = Nothing

Main = DTSTaskExecResult_Success
End Function


"Seb" wrote:

Hi fsanchez

I've never actually tried to call GetExecutionErrorInfo in an ActiveX
script - I'm using VB. It would be great to be able to do this, but as
I found when researching my problem, it looks as though this isn't
possible. The quote below is from Darren Green - I wish he was wrong
about this but unfortunately for your problem he does know what he's
talking about!

See further below for possible workaround

There may be a workaround to your problem, IF you can replace your
ActiveX script with a Run Package task. I've never tried to do this,
but you may well be able to set the child package's parameters (which
you do through the Package/Package2 object in your script) with a
Dynamic Properties task instead in the main package.
If you can use a Run Package task, then you could wrap the execution of
the main package in a VB wrapper (using pretty much the same code as
you use above), and add some extra-clever functionality to the
error-detection part of your code:

[can't remember the exact object model, so this is very pseudo]
For each Step in MainPackage
If Step.ExecutionStatus = [completed] and
Step.ExecutionResult=DTSStepExecResult_Failure Then
'[here call GetExecutionErrorInfo, as in your code]
[this line VERY pseudo - but I think you'll get what I mean]
If Step.Task.Customtask.TypeOfTask = [Execute Package task]
Then
Set ChildPackage = Step.Task.CustomTask.Package
For Each ChildStep in ChildPackage
[call GetExecutionErrorInfo]
Next
End if
End If
Next

you'd have to handle how the routine accumulates errors: I just set up
a strErrMsgs string, then each time I find an error, rather than
exiting, append the error info to the string, preceded by a CRLF if
there's anything already in the string. In this way the final string
contains every error detected (though of course responding to/trapping
an individual error becomes difficult or impossible - but then we are
dealing with DTS, the error-handler's black nightmare...).
To be really clever this procedure could be re-designed into a
recursive procedure, in case someone ever sets up a package which runs
a child package which runs a grandchild package which runs a
great-grandchild package....

I'm going to try out this approach, I'll post results as I go. If
anyone reads this and knows it can't possible work for some reason,
please do post knocking my idea down!

regards



Seb