Hi all, I'm puzzled by some behavior in a script that launches other
packages and I'm looking for some help.

I have a main package that runs on a schedule. The heart of this package is
a Data-Driven Query, which executes a query to get the names of the other
packages that it is supposed to execute. There is one transform defined,
which executes a script, the important parts of which I've copied (by
retyping, so excuse any typos) at the end of this message. The gist of it is
this: I create a Package object, call LoadFromSQLServer to get the package I
want by name, set FailOnError to true, and the execute the package. If the
package raises an error, I record it as having failed in the destination
table, otherwise, its recorded as having executed successfully.

The script works, but the behavior when the executed package fails doesn't
make sense to me. My intent is for the main package to simply log the
failure to a table and continue executing the next scheduled package. What
happens in practice is: the Data-Driven Query task in the main package stops
running after the first executed package fails, even though the main package
is NOT set to fail on the first error. Since my script doesn't return an
error code, and clears the error object that's raised from the executed
package, I don't understand why this is happening. Subsequent (OnSuccess)
tasks still execute, and everything is logged in the DTS log as having
completed successfully. What am I doing wrong?

If I set pkg.FailOnError to false, then the main package keeps executing
packages after the first failure, but the failed packages don't get marked
as having failed in the log table (although the DTS exception logs report
failures just fine). Is there another (simple) way to make a package report
a failure?

Hoping someone can help...
Lamont Adams
MCP

-------CODE------------
Function Main()
dim pkg
set pkg = CreateObject("DTS.Package")
pkg.LoadFromSQLServer "Server", , , 256, , , , DTSSource("Package_Name")
on error resume next
pkg.FailOnError = true
pkg.Execute
on error goto 0
if err.number <> 0 then
DTSDestination("Error") = 1
DTSDestination("Result") = err.Description
err.clear()
else
DTSDestination("Error") = 0
DTSDestination("Result") = "Completed Normally"
end if

Main = DTSTranformStat_UpdateQuery
End Functon

------------END CODE-----------