Post reply

Purposely stopping a job at a specific step.....

  • November 3, 2008 at 7:11 am

    #200865

    All,

    Here is my scenario...

    I have a job set up in SQL Server 2005. The job has 5 separate SSIS packages that are run in a specific order. I want to add a step as the very first step in the job to perform a validation check before the second step runs. The validation is simply to see if there are contents in two tables. If the validation fails, I want the whole job to stop. If it passes, I want to run the other steps in the job. What I am unsure of is how to stop the job if the validation fails.

    Any help is much appreciated...

    Thanks,

    Benny Blanco

  • November 3, 2008 at 8:01 am

    #893019

    I can think of 2 ways

    1.Combine all 5 packages into 1 big package, with Control & Data Flow

    and you do the validation between the flows

    2. in SQL Agent, add a step between the package

    check the contents/count of the table, if succeed return SUCCESS (0?); otherwise return ERROR/EXCEPTION (which should cause the failure of the job)

    and for the job, set to "Quit Job reporting Failure" on Failure

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • November 3, 2008 at 8:07 am

    #893026

    Thanks for your reply! Just before I got your reply I tried a feature that I found in BOL. It made life A LOT easier for me. It is a procedure called sp_stop_job. When I run that after a failed validation and pass in the job's name, it stops itself and does not continue to the next step!

    Thanks again!!

    -B

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply