is there an elegant way for agent notification to show failure if any step fails

  • hi our corp dw load sqlagent job goes to the next step on the 14+ erp loads if they fail.  but quits notifying of failure on any cube step at the end.  most of the loads are ssis but there is some t-sql too.

    without making the job really ugly, is there an elegant  way for the email notification to show failure even if one of the erp load steps failed?   if possible external to the etl tool (ssis,t-sql) used in each step?

    notif

  • Do you want the job to fail immediately, and stop, in the event of failure of any steps? Also, you want the e-mail to come from SQL Agent itself, is that right?


  • Answer Phil's questions, but the thing I might think about is having a final step that looks at sysjobhistory and then sends a note based on any failures of any steps.

  • For jobs like these, I have a proc that goes through sysjobhistory. If there are any failures it passes the messages along to my usual SQL Agent Job failure proc which shoots out a nicely formatted HTML email. I just hate seeing that generic "The job failed ..." message without any details.

    --------------------
    Colt 45 - the original point and click interface

  • hi phil, sorry for the delay.   no we purposely want it to go to the next step so as many erps can load as possible  before human beings step in.  and i dont think it would matter where the email came from.

    • This reply was modified 3 weeks, 3 days ago by stan.
  • thx philcart.   i may resort to that.  from what i remember the log is 99% accurate, sometimes just simply missing data.   i may have to pepper something somewhere not coupled to your solution with a notification that the job is running too long.   our 4 main failures are duration, duplicates, dropped connections and believe it or not data types.

    • This reply was modified 3 weeks, 3 days ago by stan.
  • OK, so all of the steps need to be configured as 'On failure: go to the next step'

    Your final step should be one which collects details of any execution errors from the preceding steps and sends them in an e-mail, as suggested by PhilCart.


  • thx phil.  sorry for the late response.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply