I am using SQL Server 2019. I have a few job steps. The first step is to kick start a few child jobs. For instance, SQL Server agent parent job in which the 1st step is to execute 4 different child jobs 1, 2, 3, 4 and the 2nd step is to execute another job child job 5, after starting the child job 1, how to wait until child job 4 is successfully completed before moving on to the 2nd step which is to execute child jobs 5, 6, 7 which has dependency on the jobs 1, 2, 3, 4, But I want to make sure all the prior jobs 1, 2, 3, 4 completed successfully? How would I check for completion of 1st step until however long it takes and then start the execution of step 2?

I tried building my logic using the below queries but it does not work as expected.

Queries:

EXEC msdb.dbo.sp_start_job @job_name=N’child_job_1′; EXEC msdb.dbo.sp_start_job @job_name=N’child_job_2′; EXEC msdb.dbo.sp_start_job @job_name=N’child_job_3′; EXEC msdb.dbo.sp_start_job @job_name=N’child_job_4′

SELECT *
FROM msdb.dbo.sysjobhistory
WHERE job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE [name] = 'JobName') 
AND step_id = 0
AND run_date = (SELECT MAX(run_date) FROM msdb.dbo.sysjobhistory WHERE job_id = '1234' AND step_id = 0)
AND run_time = (SELECT MAX(run_time) FROM msdb.dbo.sysjobhistory WHERE job_id = '1234' AND step_id = 0)
ORDER BY run_date DESC, run_time DESC;

SELECT sj.name
  ,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
 FROM msdb..sysjobactivity aj
 JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
 AND aj.start_execution_date IS NOT NULL -- job is currently running
 AND sj.name = 'JobName'
and not exists(
    select 1
    from msdb..sysjobactivity new
    where new.job_id = aj.job_id
      and new.start_execution_date > aj.start_execution_date )