How to implement SQL Server Job that waits to another job finish?

Question

Hi,

I'm looking a way to implement a precedence step on SQL Server Agent that will verify is a specific job is running. If so, the step will entry in a "wait" state until first job finish with success.

To clarify, this will be the cenario:

Job Name: 'My Job'

Job to be checked: 'Validate tables'

Steps of 'My job':

Step 1: Check if 'Validate tables' is running Step 2: Do something else

On Step 1, need to use some T-SQL like this: exec msdb.dbo.sp_help_job @job_name='Validade tables',@job_aspect ='JOB'

If the current_execution_status is equal to 1, then will wait (will not run the step 2)

I'm not an expert on SQL Server Agent, so I need some help on this.

Thanks in advance.

Answer 1

It's look like you need SSIS package.

Answer 2

Hi ,

Why don't you use a third job and stick these two jobs as Step one and Step two in the third job. That way it wolud not over lap and only run if First Step completed.