March 6, 2017 at 10:59 am
Hi,
Need to send email on package failure and continue with running job. How can I do this?
Can anyone explain step by step?
March 6, 2017 at 1:22 pm
I won't give you a step by step but I will give you the higher level things you should look into to make this work.
The very first is the OnError Event Handler.
Once you're in there look at the system variable Propogate.
You might also find the system variable Error Description useful (only in the OnError Event Handler).
And look into the Send Mail task.
If you need something more complicated than a simple text email then you'll have to script it.
Start looking into these and let us know about specific questions you can't figure out from online help.
March 6, 2017 at 2:34 pm
If I understand correctly, you have a SQL job that calls multiple things, one of which is an SSIS package that sometimes fails and you want it to continue when a step fails, correct?
If so, open up the job properties, open up the job step and go to Advanced. Here you can set what happens if the job step fails or succeeds. You can even have the job fail on job step success if logic dictates that should happen or restart the whole job if any job step fails or whatever you like.
Now the fun part comes in - The best way I can think to do this (although the experts may have a better idea) is to have multiple emails; 1 per step.
So if you have a 5 step job, you'd have 5 emails as well. So if job step 1 fails, it would call email step 1 and when email step 1 finishes, it would start step 2. If Step 2 fails, it would call email step 2 and when that finishes it would start step 3.
Messy, but I think it gets your job done, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 6, 2017 at 3:28 pm
JustMarie - Monday, March 6, 2017 1:22 PMI won't give you a step by step but I will give you the higher level things you should look into to make this work.The very first is the OnError Event Handler.
Once you're in there look at the system variable Propogate.
You might also find the system variable Error Description useful (only in the OnError Event Handler).
And look into the Send Mail task.If you need something more complicated than a simple text email then you'll have to script it.
Start looking into these and let us know about specific questions you can't figure out from online help.
I worked by adding "event handler " for " on error" and added execute sql task to send email through "sp_db_sendmail" sproc.
Thanks for the reply.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply