1

I have a multi-step process that sometime should stop in the middle, not because of any failure but for business reasons which are not failures.

How can I tell Agent to successfully exit the whole job? (Using "Quit the job reporting success" throws a dreaded Yellow Triangle for the job, and the Red Circle for the step, even though there are no errors.)

Since there are many steps, any one of which might legitimately fail, I'd rather not lump everything into one giant Step.

2

One solution is to use multiple job steps and branch depending on the success/failure condition of the business condition - but that means the job will produce that "dreaded" yellow triangle and red circle for the step.

A second solution is to use 3 jobs and call one of two jobs based on the result of the business condition.

STEP 1 : Do something

STEP x : Test for business condition

    IF dbo.fnBusinessCondition = 1 
       EXECUTE msdb.dbo.sp_start_job 'Job2'
    else
       EXECUTE msdb.dbo.sp_start_job 'Job3'
1

You might want to consider a SSIS package to incorporate your conditional execution logic.

Assuming you have a two-step SSA job, both executing T-SQL statements, you could create a SSIS package with three Execute SQL tasks.

  1. Task 1 would execute the SQL for your first business process.
  2. Task 2 would execute a check, like for the result of a control table, which would determine if Task 3 would execute. Task 1 connects to Task 2 with a Success constraint type precedence constraint. Task 2 assigns a result to a SSIS variable.
  3. Task 3 would execute the SQL for your second business process. It would be connected to Task 2 with a Success constraint AND Expression precedence constraint. The expression would be the evaluation of the variable from Task 2. If the result is valid for execution of Task 3 it proceeds, else it just stops.

You can then deploy the SSIS package and schedule it for execution in the SSA job as a job step like the original T-SQL steps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.