Unanswered: DTS "On Failure" workflow not run - SOLVED (I think)
I hope I've done due dilligence before posting for help. I've combed a lot of Google and dBforums search results with no luck.
Anywho, this *seems* like it should be simple: I have a Transform Data Task, into which I've introduced deliberate errors, followed by an ActiveX Script Task that is supposed to fire based on the failure of the Data Transform Task. The second task is joined to the first via an "On Failure" workflow step.
The problem: The second task never fires. The first task fails as expected, but the second one just shows the "Not Run" indicator in the package results after executing.
I've experimented with the transaction settings in the package properties, as well as the transaction settings in the workflow properties of each step. I don't particularly want the package as a whole to be couched in a transaction, but if that is part of the equation for making the "On Failure" step fire, I'll happily go along. Also, in the Data Transform task with the deliberate errors, I have the max error count set high, as I want the task to continue logging errors for each record that chokes. I've tried various settings there as well, however.
Thank you in advance -- I'll try to contribute more and leech less after this.
Well now I just feel silly, not to mention chagrinned for posting in such a whiney tone.
I was executing the package from an Enterprise Manager session running on my workstation. The package is owned by the account under which the SQL service and SQL Agent service run. As soon as I logged into the SQL Server as that user and executed the package from EM, the On Failure step fired as expected. Seems a little picky of DTS, but I ain't arguing.
As it turns out, the Max Error count for the Data Transfer task *was* related. I thought I'd reasonably tested the bounds of that, but apparently I didn't. If the max error count wasn't reached in the execution of the task, it would log all the errors that did occur and not fire the On Failure task -- even though the task results still showed failure.
In fact, I think now that my conclusion that the problem was caused by executing from Enterprise Manager on my workstation was misplaced.
Great testing / troublshooting skills, eh?
It seems a little illogical -- it seems like the "On Failure" step should fire *every time* the source step finishes reporting failure, but I guess not.