I am using example from sqldts.com on calling a dts from a dts. i can
trap errors from steps, but how do i get error results from the
oPkg.Execute
statement?

Basically, we have a job that runs DTS packages on demand. users put the name
of a DTS package in a table and we then extract that and use it in an ActiveX
script to execute packages. issue is what happens when they put in the wrong
name for a package. it fails and stops. i want to capture that and ignore the
error and then move to next package in list. now it failes and stops.



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

'************************************************* ***********************
' 205 (ExecPkgFromPkg)
Option Explicit


Function Main()



Main = DTSTaskExecResult_Success

dim countr
dim RS
set RS = CreateObject("ADODB.Recordset")
set RS = DTSGlobalVariables("gvDTSPackagesToRun").Value

Dim oPkg, oStep

Dim sServer, sUID, sPWD, iSecurity , sPkgPWD, sPkgName,
sErrMsg


for countr = 1 to RS.RecordCount



Set oPkg = CreateObject("DTS.Package")



MsgBox(sPkgName)
' Assign parameters
sServer = "local"
sUID = ""
sPWD = ""
iSecurity = DTSSQLStgFlag_UseTrustedConnection
sPkgPWD = ""
sPkgName =RS.Fields("dts_package_name").value
MsgBox "line 23 after setting parameters and getting package name"
MsgBox(sPkgName)

' Load Child Package
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity ,
sPkgPWD, "", "", sPkgName

oPkg.FailOnError = TRUE
MsgBox "line 27 after package loading"

' You can amend the child package here, e.g.




' Execute the Package
oPkg.Execute

MsgBox "line 42 after execute"
if DTSTaskExecResult_Failure = 1 then
MsgBox "task failed"

end if
' Pass a child value or variable back to the parent, after
execution e.g.
'DTSGlobalVariables("ParentVar").Value =
oPkg.GlobalVariables("ChildVar").Value

Dim sMessage, lErr, sSource, sDesc

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else

End If
Next


MsgBox "line 78 after error checking"
' Display Results
MsgBox sMessage



' Clean Up
oPkg.Uninitialize
Set oStep = Nothing
Set oPkg = Nothing


RS.MoveNext
Next


' Clean Up
oPkg.Uninitialize
Set oStep = Nothing
Set oPkg = Nothing

End Function

Guess you already had a look at this?

http://www.databasejournal.com/featu...le.php/1461441

Although that link doesn't really help you any further.

You can catch and examine the error using the static Err object, like this:

on error resume next
dim oPkg
set oPkg = createobject("dts.package")
oPkg.LoadFromSQLServer bla bla bla
oPkg.FailOnError = TRUE
oPkg.Execute

if Err.number <> 0 then
msgbox "caught:" & Err.description
Err.Clear
end if

Main = DTSTaskExecResult_Success

The runtime error will still fire after handling it!

To prevent executing a package does not exist, you can implement to following
code. It iterates over all packages on a server to check if a package with
sName exists. I am sure you can optimize the code, it is just to illustrate
the feature. (Replace <server> with your server's name and remove the
msgboxes). Use blnContinue to determine if you want to execute the package
(true) or not.

Function Main()

dim oApp, oPkgServer, colPkgInfo, oPkgInfo

Set oApp = createobject("DTS.Application")
Set oPkgServer = oApp.GetPackageSQLServer("<server>", "", "",
DTSSQLStgFlag_UseTrustedConnection)
Set colPkgInfo = oPkgServer.EnumPackageInfos("", True, "")

blnContinue = false
Set oPkgInfo = colPkgInfo.Next

if oPkgInfo.name <> sName then
Do Until colPkgInfo.EOF
msgbox opkginfo.name
Set oPkgInfo = colPkgInfo.Next
if not colPkgInfo.EOF then
if oPkgInfo.name = sName then blnContinue = True
end if
Loop
else
blnContinue = True
end if

msgbox blnContinue

Main = DTSTaskExecResult_Success

End Function