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?
May 16, 2025 at 11:17 am
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?
May 16, 2025 at 9:41 pm
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
May 19, 2025 at 12:10 pm
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.
May 19, 2025 at 12:13 pm
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.
May 19, 2025 at 5:02 pm
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