ActiveX Task Doesn't Follow Workflow

[rickj65]

In my package, I have an ActiveX Script Task set up that essentially checks for records in a table. Within the ActiveX Script, if the records exist, I set

Main = DTSTaskExecResult_Success

If there are no records, I set

Main = DTSTaskExecResult_Failure

I've set up the DTS workflow so that if the ActiveX Script task is successful, the package continues to the next step (using the Green "On Success" workflow arrow). If the task fails, the package goes to a different step (using the Red "On Failure" workflow arrow).

The problem I'm having is that when my ActiveX Script fails (as expected), the entire package fails without proceeding through the subsequent workflow steps to handle the failure. I get the error message:

"The task reported failure on execution"

And then when I hit OK, the whole package stops.

I'm not sure why this is occurring. I've tried setting Main equal to DTSStepExecResult_Success and DTSStepExecResult_Failure as well, but no luck. Also the "Fail Package On Step Failure" checkbox in the Workflow options is not checked.

Any insight would be appreciated.

Thanks,
Rick

 

[broniusm]

Rick-
Is the problem still occurring, or have you resolved it?

Your question sparked my interest, because I was having the same problem. I'd like to share with everyone what I changed to solve the issue, but I don't know! It works exactly as described above, but properly fails the step, not the package.

 

[nigelrivett]

How about the package property - fail package on first error

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[krbrewer]

Rick,
While searching the web, I came across your post. You have covered the exact problem that I am currently encountering! I was wondering if you or anyone else has come up with the solution.

In one of my DTS Packages, I have an ActiveX Script Task that basically functions as a point on a decision tree. The task tests a situation and if it evalutes to true, then I set Main equal to DTSStepExecResult_Success and the process continues down the On Success workflow to the next task in the process. If the test evaluates to false, then I set Main equal to DTSStepExecResult_Failure, and the process is supposed to continue down the On Failure Workflow. Unfortunately, when the test evalues to false, I get the same error message that you described in your post. And when I clear that message, the entire DTS Package stops executing. I have double and triple checked the property and option pages for each object in the package as well as the package itself. None of the checkbox options for failing the package on step error are enabled.

Unfortuantely, I have not yet found anything on the Internet to help me solve this problem. Any ideas on resolving this problem?
Thanks!
Ken

 

[nigelrivett]

You can always create two steps leading from this one. Both with on success workflow and both disabled.
The activex step then enables the relevant step and queues it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rickj65]

Unfortunately I never figured out how to resolve this issue. I did come up with a workaround that is probably more specific to my situation, but here it is anyway...

Rather than checking for records in the ActiveX Script and forcing a Success/Failure result, I created a SQL procedure after the ActiveX script runs. Within the SQL procedure I use IF...ELSE logic to determine my course of action within SQL.

IF (SELECT COUNT(*) FROM MyTable) > 0
BEGIN
EXEC MyStoredProc
END
ELSE
BEGIN
EXEC ADifferentStoredProc
END

This worked, but still, there should be a way to handle the program flow at the package level based on the ActiveX Script/Task result.