MSSQL - Run A Scheduled Job from SQL Script

  -- Invoke SSIS package to copy tables to ST

  DECLARE @JobStatus INT, @TimeOutCnt INT, @LastRunOutcome INT

  EXEC msdb.dbo.sp_start_job 'SQLJob1'

  ---- wait until job is completed; current_execution_status = 4 indicate the completion of the execution

  WAITFOR DELAY '00:00:03'

  SELECT @TimeOutCnt = 100, @ErrorMessage = ''    -- set maximum wait time to 5 minutes

  SELECT @JobStatus = current_execution_status, @LastRunOutcome = last_run_outcome FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC MSDB.dbo.sp_help_job @job_name = ''SQLJob1'', @job_aspect = ''JOB'' ')

  WHILE @JobStatus <> 4 AND @TimeOutCnt > 0

  BEGIN

    WAITFOR DELAY '00:00:03'

    SELECT @JobStatus = current_execution_status, @LastRunOutcome = last_run_outcome FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC MSDB.dbo.sp_help_job @job_name = ''SQLJob1'', @job_aspect = ''JOB'' ')

    SELECT @TimeOutCnt = @TimeOutCnt - 1 

  END

  IF @TimeOutCnt = 0   -- job has timed out; unknown reason

  BEGIN

    SELECT @IsError = 1, @ErrorMessage = 'Executing package SQLJob1 timed out.'

  END

  ELSE IF @LastRunOutcome = 0  -- job execution failed

  BEGIN

    SELECT @IsError = 1, @ErrorMessage = 'Executing package SQLJob1 failed.  See job history for details.'

  END