MS SQL Server DTS - DTS Workflow on fail (fails)

Author

DTS Workflow on fail (fails)

bnhcomputing

2005-09-28, 8:24 pm

I have developed a DTS package. The package consists of several active X
script tasks, and several transform data tasks. When all the task suceed, it
works great.

However, If I return _failure from one of the active X scripts, execution
stops. The failure workflow path in NOT followed.

Any ideas?

Thanks
--
bnhcomputing

Ilya Margolin

2005-09-29, 9:24 am

bnhcomputing,

Make sure that 'Fail package on first error' in Package Properties is not
checked and the failing step does not have 'Fail package on step failure'
checked in the Workflow Properties. Otherwise the package is reported as
failed and nothing is executed after that, even on failure constraints.

Ilya
"bnhcomputing" < bnhcomputing@discuss
ions.microsoft.com> wrote in message
news:1CA47615-CA07-4990-82E9- B6BE7FB247B8@microso
ft.com...
> I have developed a DTS package. The package consists of several active X
> script tasks, and several transform data tasks. When all the task suceed,
it
> works great.
>
> However, If I return _failure from one of the active X scripts, execution
> stops. The failure workflow path in NOT followed.
>
> Any ideas?
>
> Thanks
> --
> bnhcomputing

bnhcomputing

2005-09-29, 9:24 am

Fail package on first error in NOT checked.
Fail package on step failure is NOT checked.

Active X task 1

Function Main()
On Error Resume Next
Main = DTSTaskExecResult_Fa
ilure
End Function

Active X task 2

Function Main()
Dim ErrorMsg
ErrorMsg = "ERROR #:= "+CSTR(Err.Number) +" | " + Err.Source + " |
"+Err.Description
Err.Clear
Main = DTSTaskExecResult_Su
ccess
End Function

DTS tells me "one task(s) failed during execution".
The executing DTS package window shows:
Package 1 Error Occurred
Package 2 Not Run

Both the success and fail paths point to Package 2.

Open to suggestions
--
bnhcomputing

"Ilya Margolin" wrote:

> bnhcomputing,
>
> Make sure that 'Fail package on first error' in Package Properties is not
> checked and the failing step does not have 'Fail package on step failure'
> checked in the Workflow Properties. Otherwise the package is reported as
> failed and nothing is executed after that, even on failure constraints.
>
> Ilya
> "bnhcomputing" < bnhcomputing@discuss
ions.microsoft.com> wrote in message
> news:1CA47615-CA07-4990-82E9- B6BE7FB247B8@microso
ft.com...
> it
>
>
>

MarkT

2005-09-29, 8:24 pm

Sounds like you have two workflow constraints on package 2 (fail and
success)??? If so, dts will and the constraints together so that package 2
will execute only if package 1 fails AND succeeds. Try a single On
completion constraint to package 2; I'm also having a problem with this type
of workflow but maybe it will work for you.

"bnhcomputing" wrote:
[color=darkred]
> Fail package on first error in NOT checked.
> Fail package on step failure is NOT checked.
>
> Active X task 1
>
> Function Main()
> On Error Resume Next
> Main = DTSTaskExecResult_Fa
ilure
> End Function
>
> Active X task 2
>
> Function Main()
> Dim ErrorMsg
> ErrorMsg = "ERROR #:= "+CSTR(Err.Number) +" | " + Err.Source + " |
> "+Err.Description
> Err.Clear
> Main = DTSTaskExecResult_Su
ccess
> End Function
>
> DTS tells me "one task(s) failed during execution".
> The executing DTS package window shows:
> Package 1 Error Occurred
> Package 2 Not Run
>
> Both the success and fail paths point to Package 2.
>
> Open to suggestions
> --
> bnhcomputing
>
>
> "Ilya Margolin" wrote:
>

MarkT

2005-09-30, 11:23 am

I haven't tried it yet but other discussion threads have provided a link for
skipping tasks:
http://www.sqldts.com/default.aspx?218

"MarkT" wrote:
[color=darkred]
> Sounds like you have two workflow constraints on package 2 (fail and
> success)??? If so, dts will and the constraints together so that package 2
> will execute only if package 1 fails AND succeeds. Try a single On
> completion constraint to package 2; I'm also having a problem with this type
> of workflow but maybe it will work for you.
>
> "bnhcomputing" wrote:
>

Sponsored Links