asked on

DTS workflow problem: On_Failure task not executed, package fails

Hi,

I have the exact same error that was discussed at http://www.tek-tips.com/viewthread.cfm?qid=741644 with no solution.
In short, I return DTSStepExecResult_Failure (or DTSTaskExecResult_Failure, doesn't make a difference) from an ActiveX task and the task I defined to be executed on failure doesn't get executed but therefore the whole package fails.

Does anyone have an idea?

Thanks in advance,
AIndy

Brendt Hess

Sometimes, it depends on what the failure that you are trapping is. Certain errors encountered by an ActiveX task are not handled by the standard error handler. Instead, an unhandled (by anything) error is generated.

You may want to include a test in the On_success branch for the DTCStepExecStatus, and see if it is <> 4 (Completed). If so, you should throw an error.

Some discussions also talk about this issue (as you have seen at tek-tips) without coming to a conclusion. The above are my opinions.

One final place to look for assistance is this message series on Google:

http://groups.google.com/group/microsoft.public.sqlserver.dts/browse_thread/thread/b59c6da0cb3b752b/152d37c1ec0f45b4?lnk=st&q=DTSStepExecResult_Failure&rnum=1&hl=en#152d37c1ec0f45b4

It discusses a DTS helper COM object to probe error values and others inside an ActiveX script. Looks like it would be very helpful to you.

Anthony Perkins

Post your code from your ActiveX script.

AIndy

ASKER

Thanks bhess, acperkins.
Bhess, what do you mean by the test in the On_success branch? The on_success branch works fine, the other one doesn't..

The relevant code from my Active X script is

       If objFSO.FileExists(workPath & focusFile) then
       begin
       objFSO.CopyFile workPath & focusFile, workPath & "input.xls", true
       Main = DTSTaskExecResult_Success
       else
       Main = DTSStepExecResult_Failure
'Main = DTSTaskExecResult_Failure
       end if

I assured that the else-branched is reached in case the file doesn't exist.

AIndy

Anthony Perkins

Here is the correct syntax:

If objFSO.FileExists(workPath & focusFile) then
objFSO.CopyFile workPath & focusFile, workPath & "input.xls", true
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if

AIndy

ASKER

acperkins, in how far is that supposed to make any difference?

Anthony Perkins

>>in how far is that supposed to make any difference?<<
Well, for one it will run. Your syntax won't even run. There is no Begin in a VBScript.

AIndy

ASKER

Well, it did run before. However, do you have any idea concerning the real problem?

Anthony Perkins

>>Well, it did run before.<<
Fair enough. Good luck.

AIndy

ASKER

I guess that's a "no"?
Anyway, thanks for the syntax-hint...

Anyone else have an idea?

Anthony Perkins

Not exactly, it just means I no longer wish to contribute to this thread.

AIndy

ASKER

Don't get offended so quickly. I am sorry that I didn't treat your syntax hint with more respect, but believe me somehow this was ignored in my Script and it did run...
I hope I didn't hurt your feelings...

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AIndy

ASKER

Hi Acperkins,

thanks for your help. I actually found the mistake in my workflows. The problem wasn't the ActiveX-Script or its return-code, it was actually that the script on the error-branch was also dependent on the success of another task which itself was dependent on the success of the original ActiveX script. That's why the task on the error branch was never executed. Even though this sounds like a stupid mistake, it actually made sense at the time I designed it because I wanted the same script (which handled a loop) to be run at the end of two different branches.

Anyway, I found this out after reducing the complexity of the workflow according to the test you suggested. Therefore you deserve many thanks and the points :-)

AIndy