I have most of my jobs set up to email me either when they fail or when they complete, depending on how sensitive the job is. If I need to know it is taking too long, I set it up to email me on completion. This works fairly well, but some of these jobs run in the middle of the night and I need to be woken up if they fail. An Email is nice, but what would be great is a text to my phone. I checked the Notifications tab on one of the jobs and the options it lists are: Email, Pager and NetSend. The latter two of these are pretty dated and I could have sworn that the last time I looked in BOL, Pager and NetSend were depreciated. I was surprised Text Message isn’t one of the options. Still, all was not lost. There’s a way to send text messages to phones using Email.
I went in to my profile under Operators and under EMail Name I put in my text email address right after my work and home addresses. The texts you get aren’t pretty, but really all they need to do is let you know you need to log in and check what’s going on.
Some of the jobs need to notify me and a colleague. I have a special operator set up to email both of us. I went to add both of our text email addresses to the operator, but I ran into a problem; The EMail Name field for Operators has a character limit of 100 characters. Since I already had 3 email addresses in there, I hit the limit long before I was able to complete a text email address. It took me a few tries to figure out what was going on because it doesn’t notify or warn you that you’re over the limit, it just truncates as it saves and then when used it sends to any valid emails, ignoring the rest.
Since there’s no way to add multiple operators to a notification event, there are only 2 ways to get around this. The first is to set up a group email in Outlook with all the emails you want in it and use that as the email address in the special operator. This can be the easiest, but if you don’t have the authority to create and modify these groups, you have to go through someone else every time you want to make a modification or create a new group which can add unacceptable time to the process.
The other way is more difficult and fiddly. You can send emails by creating an SSIS package that sends the email to as many people as you want (I have some that send emails to 8 people with no problem), but then you have to change your job so that if any step fails, it goes to the email step and if no step fails, it never goes to that step. If you have a job with many steps, this can become tedious. However, it does allow more flexibility in processing the job so that if there are some steps that can fail without needing the whole job to stop, you can still get an email while allowing the job to continue. Unfortunately, that increases the complexity yet again as you need to create a step for every email that could possibly go out.
Since there is no way to tell the email step that if it was called by step 6 it should go to step 7 and if it was called by step 12 is should go to step 13, you need a separate step for each that will indicate where to resume the job. For most jobs, the user group is the proper method, but I do have some that are complex enough when it comes to when it can continue and when it needs to stop that I may end up creating the SSIS email job and adding another dozen steps to allow it to email me, but allow the package to continue.