The other day I was asked How to notify multiple operators using database mail for failed jobs and a different operators for successes.
First I looked at the operator email addresses field firstname.lastname@example.org;email@example.com….etc is not helping as there is a limit on the characters in the email name entry of operator and we still have the problem we need different groups for success and failures.
So this is what I came up with as a workaround if the intention is that multiple people in your organization should be notified if a job fails and a different group of multiple people for successes.
You will notice the Steps 1-3 are the normal tasks that the schedule job is uses for as you would do for your task. There can as many steps as needed before these but the last step (Step 3) of the process need to break “On Success” and “On Failure” to go into separate emails. Also all “On Failures” need to continue to your “Failure Email” as highlighted below. So the Failure group gets there emails and the job will still fail for the historical records.
You will see the option to change the direction of the “On success action” and “On Failure action” in the Advanced tab of the Job steps.
Failure Email Step -General Property
Failure Email Step -Advance Property
Success Email Step -General Property
Success Email Step -Advance Property
I hope this helps. Happy Coding. Have FUN!