I have a package with several steps (execute package tasks) which
execute other DTS packages. Attached to each step, I have an ActiveX
workflow script to check the exection of the previous step (the
ultimate purpose will be to act as a failure step and send mail).
This script, as expected, runs prior to the execution of the execute
package task.

However, the DTSStepExecResult value is not returning correctly based
on the execution of the previous step (the execute package task). The
previous step fails with the error "Column name 'column' was not
found." This is an expected error from the package. The
DTSStepExecResult for the previous step returns 0
(DTSStepExecResult_Success) and 4 (DTSStepExecStat_Completed) for
status, so I know it has executed. It should be returning
DTSStepExecResult_Failure.

Both execute package steps (tasks) are set to execute on main thread
and fail package on error is false. I have also tried the setting
off, and it executes with the same issue. The ActiveX workflow for
the step after the failing step is as follows (normally an 'if'
statement would check and then DTSStepScriptResult would be set
accordingly):

Function Main()

msgBox DTSGlobalVariables.Parent.Steps("ExtractOrders").E xecutionResult
msgBox DTSGlobalVariables.Parent.Steps("ExtractOrders").E xecutionStatus

' check all steps just to make sure
for each step in DTSGlobalVariables.Parent.Steps
msgbox step.Name & ": " & step.ExecutionResult
next

Main = DTSStepScriptResult_DontExecuteTask
End Function

If you have seen this or know what might be incorrect to fix it,
please let me know. Standard DTS package and not executing through VB
or VB.NET.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Thanks, Kevin

An execute package task will only fail through a child error if the *child*
has fail on first error set to true.

The exec package task worked, it executed the child package, so is a child
failure a failure of the parent task? How does a child execution failure
compare to a failure to even load the child? The answer is it can be both
hence you need to set the option for what you want in the child. Not always
ideal, but that is the way it works.


--
Darren Green
http://www.sqldts.com


"Kevin Lloyd" <klloydsqldba (AT) yahoo (DOT) com> wrote

Thanks, Darren, and you're right, it's not ideal. My expectaiton is it
passes the error up the chain, but, again, DTS does something we can't do
anything about. It's a step error within a package and not a package error.
The problem here, though, is that a failure in the child
*fails* the parent as well, which is *not* what I want.

Unfortunately, I think I'll need to resort to the following ActiveX script
task in order to truly get the logic I want. Also unfortunate is I can't
get a hold of GetExecutionErrorInfo through VBScript. This may not be the
best solution, but I've been messing with a workaround long enough.

On a side note, do you know of a way to execute a specific function within
an ActiveX Script Task or pass it variables? I can't think of any outside
of writing VB code and creating custom functions.

Thanks, Kevin

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

Option Explicit

Function Main()

dim oPkg, errCode

set oPkg = createobject ("DTS.Package")

'could create an RS and loop all packages to execute
oPkg.LoadFromSQLServer "(local)", , , 256, , , , "TIMSS STG_Order
Extract"

oPkg.Execute
errCode = tracePackageError (oPkg)
if errCode <> "" then

set oPkg = nothing
msgbox errCode
DTSGlobalVariables.Parent.Steps("SendFailureMail") .Execute
Main = DTSTaskExecResult_Failure
else

set oPkg = nothing
end if
' end loop here

Main = DTSTaskExecResult_Success

End Function


Function tracePackageError(oPackage)

Dim ErrorCode
Dim ErrorSource
Dim ErrorDescription
Dim ErrorHelpFile
Dim ErrorHelpContext
Dim ErrorIDofInterfaceWithError
Dim i

For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
'oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource,
ErrorDescription
tracePackageError = "Step """ & oPackage.Steps(i).Name & """ of package
""" & oPackage.Name & """ failed."
End If
Next

End Function



"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

In message <esUOj3BzEHA.344 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Kevin Lloyd
<lloydk (AT) _nospam_quaero (DOT) com> writes

There is an Exec (or maybe Execute I forget) statement in VBScript which
can execute stuff, from a string. Have a dig in the VBScript docs on
MSDN for more info.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org