I have an agent job that will have 80 steps.
For example, if the query in step 2 returns rows, I don't want to run the remaining 78 steps. I want the job to go to the last step (named Step X).
Is it possible to force the job to go to a certain step while it is running? I don't want to force the job to fail via RAISEERROR.
I know SSIS is better suited for this need but, with 80 steps, I prefer to use a SQL job.
Thanks for any tips.
Why would you have a step that only counts rows. Why not put it in the step that needs that count and just simply return if does not meet condition
For the "On success" and "On failure" options, you can specify "go to step #".
Let's say you set "On success" as "Go to step 80 / X". Then, if you get rows returned from step 2, end the step successfully. If you don't, cause the step to fail, and set "On failure" as "Go to the next step." And repeat for other steps as needed.
2 Likes
I would recommend a redesign of this process - it might be better to use a master procedure that calls individual procedures for each 'step'. Or a single procedure that executes each 'step' and checks for data returned.
Almost certainly the next question is going to be how to log which step was successful - and that is just going to add more code to each job step that is much easier to handle in a stored procedure.
Before I went down that path though - I would reevaluate the process. I just can't see a justification for running one of 80 different steps - and actually needing to run every step prior to the 'successful' step. I can see this causing issues when someone asks why step 36 ran and there were results when it really should have been step 58 - and running step 58 separately also returns valid results.