Fog Creek Software
Discussion Board




SQL DTS "On Failure" workflow

I swore I'd never use JoS as a support forum except as a last result, but it's come to this.  I've sifted through a few hours worth of Google and forum searches, and, while a lot of suggestions come close, none seems to nail this problem.

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.

OffMyMeds
Friday, August 13, 2004

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.

Sorry for spamming up JoS with my whining.

OffMyMeds
Friday, August 13, 2004

There is a fundamental law of the universe that says the time it takes to realize your mistake and/or find the answer yourself after you post a public help request is inversely proportional to the time you spent banging your head and searching google before you posted.

Don't feel too bad - it's happened to everyone

Jason
Friday, August 13, 2004

Man... that sure is the truth.

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.

OffMyMeds
Friday, August 13, 2004

Something about the action of thinking through the problem so you can spell it out to someone else seems to generally get the right neurons firing so the solution becomes apparent.

Philo

Philo
Friday, August 13, 2004

*  Recent Topics

*  Fog Creek Home