Re: DTSStepExecResult, DTSStepExecStatus, and execute package task

From: Kevin Lloyd (lloydk_at__nospam_quaero.com)
Date: 11/16/04 

Date: Tue, 16 Nov 2004 15:52:32 -0500

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@reply-to-newsgroup-sqldts.com> wrote in message
news:uKF5wTAzEHA.1296@TK2MSFTNGP10.phx.gbl...
> 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@yahoo.com> wrote in message
> news:6ed68e17.0411160901.4e6f46d7@posting.google.com...
>> 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").ExecutionResult
>> msgBox DTSGlobalVariables.Parent.Steps("ExtractOrders").ExecutionStatus
>>
>> ' 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
>
>

Relevant Pages

  • Re: DTSStepExecResult, DTSStepExecStatus, and execute package task
    ... An execute package task will only fail through a child error if the *child* ... > I have a package with several steps (execute package tasks) which> execute other DTS packages. ...
    (microsoft.public.sqlserver.dts)
  • Re: Package.Execute code no longer works in Yukon
    ... Dim events As IDTSEvents ... Dim p4 As Package = New Package ... Dim xmldoc As XmlDocument = New XmlDocument ... > However the LoadFromSQLServer method is no longer part of the PackageClass> in Yukon it is insead a part of the ApplicationClass which has no execute> method. ...
    (microsoft.public.sqlserver.dts)
  • Re: Package.Execute code no longer works in Yukon
    ... Dim events As IDTSEvents ... Dim p4 As Package = New Package ... Dim xmldoc As XmlDocument = New XmlDocument ... > However the LoadFromSQLServer method is no longer part of the PackageClass> in Yukon it is insead a part of the ApplicationClass which has no execute> method. ...
    (microsoft.public.sqlserver.dts)
  • DTSStepExecResult, DTSStepExecStatus, and execute package task
    ... I have a package with several steps which ... execute other DTS packages. ... workflow script to check the exection of the previous step (the ... on the execution of the previous step (the execute package task). ...
    (microsoft.public.sqlserver.dts)
  • Re: Problem with DTS package
    ... auditing on the various servers to see which account is having the difficulty ... > I am using DTS active X in code behine to execute the package. ... > Dim pkg As New DTS.Package2 ...
    (microsoft.public.dotnet.framework.aspnet)