Send a mail if the Step Failed in Job

• Pulivarthi Sasidhar

  SSCertifiable

  Points: 6706

  More actions

  October 8, 2010 at 5:40 am

  #227757

  Hi,

  I have a job with 4 steps.

  Step1 :

  Step2:

  Step3:

  Step4:

  If my step 3 is failed i need to send a mail and execute the step 4.

  Could you please help me regarding this..

  I dont have time to Search....

  otherwise

  I have to write Script with EXEC msdb.dbo.sp_send_dbmail and add this as step 4 and move existed step4 step 5

  Thanks,

  Pulivarthi

• jeff.mason

  SSCrazy Eights

  Points: 9585

  More actions

  October 8, 2010 at 7:27 am

  #1234250

  Chowdary Pulivarthi (10/8/2010)

  ---

  Hi,

  I have a job with 4 steps.

  Step1 :

  Step2:

  Step3:

  Step4:

  If my step 3 is failed i need to send a mail and execute the step 4.

  Could you please help me regarding this..

  I dont have time to Search....

  otherwise

  I have to write Script with EXEC msdb.dbo.sp_send_dbmail and add this as step 4 and move existed step4 step 5

  Thanks,

  Pulivarthi

  Is there a reason why adding a step and reordering the job is a problem? This is probably the only way you can do it. Emails by the job are only sent when the job completes as a failure, whereas you don't want the job to end. If step 3 works go to step 5, otherwise go to step 4. Seems like a good solution to me.

• 456789psw

  SSCarpal Tunnel

  Points: 4728

  More actions

  October 8, 2010 at 12:59 pm

  #1234470

  On the step , go to advanced tab and set "On set on failure action" to go to next step

  On notification set when the job fails......

  that should work

• VastSQL

  SSC-Dedicated

  Points: 36121

  More actions

  October 8, 2010 at 3:24 pm

  #1234554

  Am with 456789psw

• preensheen

  Ten Centuries

  Points: 1362

  More actions

  October 8, 2010 at 3:29 pm

  #1234557

  Chowdary Pulivarthi (10/8/2010)

  ---

  Hi,

  I have a job with 4 steps.

  Step1 :

  Step2:

  Step3:

  Step4:

  If my step 3 is failed i need to send a mail and execute the step 4.

  Could you please help me regarding this..

  I dont have time to Search....

  otherwise

  I have to write Script with EXEC msdb.dbo.sp_send_dbmail and add this as step 4 and move existed step4 step 5

  Thanks,

  Pulivarthi

  At step 3 at send mail task and make the connector red(which mean on failure) and them drag the other connector to step four and then make the connector to blue(which mean upon completion..mean whether fail or sucess). I guess this should help

• jeff.mason

  SSCrazy Eights

  Points: 9585

  More actions

  October 8, 2010 at 7:43 pm

  #1234582

  preensheen (10/8/2010)

  ---

  Chowdary Pulivarthi (10/8/2010)

  ---

  Hi,

  I have a job with 4 steps.

  Step1 :

  Step2:

  Step3:

  Step4:

  If my step 3 is failed i need to send a mail and execute the step 4.

  Could you please help me regarding this..

  I dont have time to Search....

  otherwise

  I have to write Script with EXEC msdb.dbo.sp_send_dbmail and add this as step 4 and move existed step4 step 5

  Thanks,

  Pulivarthi

  At step 3 at send mail task and make the connector red(which mean on failure) and them drag the other connector to step four and then make the connector to blue(which mean upon completion..mean whether fail or sucess). I guess this should help

  That is SSIS, not SQL Server Agent jobs.

  I disagree with the idea of doing it the way the others suggest. If he is going on with the job to the next step if the email did not work, the job may not be "failed" and the email would be for a generic "failure" -- and the job may fail for reasons other than step 3 failing. To do what he wants and have it mean something, leave the Job Failed email for dealing with unexpected failures, and use an extra step to communicate that a known possible step failure that can be skipped if needed happened. Then simply route the job steps accordingly. I don't see why that is a difficult plan.