DTS "On Failure" workflow not run

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.

Here is a graphical illustration of the package and results: http://www.bountifulware.com/blogs/rex/dtsproblems.html

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.|||The on failure it link to the entire task not to each individual transaction within the task, there are other methods you can use to capture this event.

If you want the first failure to fail the task then set the max error count to 0.

If you want info on how you can trap each individual failed transaction let me know...|||Thanks rokslide. I did have the max error count too high. Once I set it to zero, the on failure workflow task fired the way I wanted it to.

DTS - Send Mail problem

I'm having a problem with a DTS Send Mail task when it's
tied to an On Failure workflow task. I can change it to
on success and it works. But when I change it to on
failure and then make the task fail, it doesn't work. I
created my own simple package and used the on failure and
on success and they worked fine. This particular package
imports 2 text files into a sql table and if those
transformation tasks fail, I need it to send an email. As
I said, it works on success, but not on failure. I've
tried deleting one of the tasks so that only one runs and
when it fails, the send mail task still doesn't execute.
However on the simple package that I create, it works.
Any thoughts?
Thanks,
VanCreate a simple task in the package you are having problems with and have it
fire on failure in place of the email task. See if it fires. If it does
not, I would suggest it is a logic flow issue and not a send mail issue.
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:011c01c3d473$dfbd5280$a101280a@.phx.gbl...
> I'm having a problem with a DTS Send Mail task when it's
> tied to an On Failure workflow task. I can change it to
> on success and it works. But when I change it to on
> failure and then make the task fail, it doesn't work. I
> created my own simple package and used the on failure and
> on success and they worked fine. This particular package
> imports 2 text files into a sql table and if those
> transformation tasks fail, I need it to send an email. As
> I said, it works on success, but not on failure. I've
> tried deleting one of the tasks so that only one runs and
> when it fails, the send mail task still doesn't execute.
> However on the simple package that I create, it works.
> Any thoughts?
> Thanks,
> Van|||It's definitely an issue with DTS. I created a new
package and simply copied the objects from the original to
the new package and now the send mail works on failure.
Strange that the original doesn't though. DTS has some
quirks and I believe this is one of them.
>--Original Message--
>Create a simple task in the package you are having
problems with and have it
>fire on failure in place of the email task. See if it
fires. If it does
>not, I would suggest it is a logic flow issue and not a
send mail issue.
>
>--
>Ata R
>Parvan Consulting Inc
>NO_SPAMar_alias001@.NO_SPAMparvan.net
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:011c01c3d473$dfbd5280$a101280a@.phx.gbl...
>> I'm having a problem with a DTS Send Mail task when it's
>> tied to an On Failure workflow task. I can change it to
>> on success and it works. But when I change it to on
>> failure and then make the task fail, it doesn't work. I
>> created my own simple package and used the on failure
and
>> on success and they worked fine. This particular
package
>> imports 2 text files into a sql table and if those
>> transformation tasks fail, I need it to send an email.
As
>> I said, it works on success, but not on failure. I've
>> tried deleting one of the tasks so that only one runs
and
>> when it fails, the send mail task still doesn't execute.
>> However on the simple package that I create, it works.
>> Any thoughts?
>> Thanks,
>> Van
>
>.
>|||In that case, are the package properties between the new and original
package the same?
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:002901c3d54e$e742b5a0$a601280a@.phx.gbl...
> It's definitely an issue with DTS. I created a new
> package and simply copied the objects from the original to
> the new package and now the send mail works on failure.
> Strange that the original doesn't though. DTS has some
> quirks and I believe this is one of them.
> >--Original Message--
> >Create a simple task in the package you are having
> problems with and have it
> >fire on failure in place of the email task. See if it
> fires. If it does
> >not, I would suggest it is a logic flow issue and not a
> send mail issue.
> >
> >
> >--
> >Ata R
> >Parvan Consulting Inc
> >NO_SPAMar_alias001@.NO_SPAMparvan.net
> >
> >
> >"Van Jones" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:011c01c3d473$dfbd5280$a101280a@.phx.gbl...
> >> I'm having a problem with a DTS Send Mail task when it's
> >> tied to an On Failure workflow task. I can change it to
> >> on success and it works. But when I change it to on
> >> failure and then make the task fail, it doesn't work. I
> >> created my own simple package and used the on failure
> and
> >> on success and they worked fine. This particular
> package
> >> imports 2 text files into a sql table and if those
> >> transformation tasks fail, I need it to send an email.
> As
> >> I said, it works on success, but not on failure. I've
> >> tried deleting one of the tasks so that only one runs
> and
> >> when it fails, the send mail task still doesn't execute.
> >> However on the simple package that I create, it works.
> >> Any thoughts?
> >>
> >> Thanks,
> >>
> >> Van
> >
> >
> >.
> >