SQL Server › SQL Server Agent & Job Scheduling

How would you design a complex SQL Server Agent job with multiple steps that need to run conditionally based on the success or failure of previous steps?

Advanced

Answer

You configure each job step's on success and on failure actions individually, choosing whether to move to the next step, quit the job reporting success, or quit the job reporting failure, letting you build sophisticated workflows where certain steps only run under specific conditions, such as only sending a success notification if all previous data processing steps completed correctly.

EXEC msdb.dbo.sp_update_jobstep
  @job_name = 'ETLProcess',
  @step_id = 2,
  @on_success_action = 3, -- go to next step
  @on_fail_action = 2; -- quit the job reporting failure

Real-world example A multi step data processing job is designed so that if the data validation step fails, the entire job immediately stops and reports failure, preventing potentially bad data from being loaded into the next stage of the pipeline.

Common follow-ups: What do the different numeric values for on_success_action and on_fail_action actually represent?;How do you visualize this kind of conditional job flow clearly?

Error Handling with TRY CATCH;Stored Procedures & Functions