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.

dt_ stored procedures

I am trying to use microsoft access project file to access msde databases on local workstations. I have to set the users security to db_owner due to the application requirements.
The problem users are having is since they are owners they see the dt_?? stored procedures in the queries list which are created when access addes a table to the database.
I need to keep these "Version Control" stored procedures from being created. I have tried triggers on the sysobject table but this is not allowed. The users can not delete the triggers due to sql security.
I did find out that in access if the view system objects is not check the dt_ stored procedures will not show up. But I still want to stop these from being created.
Any ideas on how to stop them from being created.
dt_ procedures are created by default (I thought only when diagrams are
created). I don't believe you can prevent them from being created, however
you could have the app use a custom query from INFORMATION_SCHEMA views that
leaves out any objects that start with dt_
http://www.aspfaq.com/
(Reverse address to reply.)
"Eric Brasher" <Eric Brasher@.discussions.microsoft.com> wrote in message
news:FAB9C710-45A0-47D2-9914-CB94D55138F4@.microsoft.com...
> I am trying to use microsoft access project file to access msde databases
on local workstations. I have to set the users security to db_owner due to
the application requirements.
> The problem users are having is since they are owners they see the
dt_?? stored procedures in the queries list which are created when access
addes a table to the database.
> I need to keep these "Version Control" stored procedures from being
created. I have tried triggers on the sysobject table but this is not
allowed. The users can not delete the triggers due to sql security.
> I did find out that in access if the view system objects is not check the
dt_ stored procedures will not show up. But I still want to stop these from
being created.
> Any ideas on how to stop them from being created.