Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    9

    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.

    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.
    Last edited by mxskweeb; 08-13-04 at 17:58.

  2. #2
    Join Date
    Aug 2003
    Posts
    9

    Solved ?

    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.

  3. #3
    Join Date
    Aug 2003
    Posts
    9

    Feeling even more silly

    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.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....

  5. #5
    Join Date
    Aug 2003
    Posts
    9

    Got it -- Thanks

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •