title: execute a SQL Server Job using SSIS package
body: <p>I am trying to execute a SQL Server 2008 job using an execute SQL task in SSIS Package.</p>
  
  <p>I used EXEC msdb.dbo.sp_start_job N'MORNING - iflagship_DataLoad_FULL';</p>
  
  <p>This works on starting the job but it returns success when the job starts . I want the EXECUTE SQL TASK to stay Yellow (Executing) until the job is finished and than turn Green (Success ) before it goes to the next step.</p>
  
  <p>Any ideas how this can be possible ? </p>
  
  <p>The below code executes the job but returns success right away even when the job is still running.</p>
  
  <pre><code>     alter procedure [dbo].[StartAgentJobAndWait](@job nvarchar(128),       @maxwaitmins int = 0) --, @result int output)
       as begin
  
       set NOCOUNT ON;
      set XACT_ABORT ON;
  
  BEGIN TRY
  
  declare @running as int
  declare @seccount as int
  declare @maxseccount as int
  declare @start_job as bigint
  declare @run_status as int
  
  set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())
  
  set @maxseccount = 60*@maxwaitmins
  set @seccount = 0
  set @running = 0
  
  
  
  declare @job_owner sysname
  declare @job_id UNIQUEIDENTIFIER
  
  set @job_owner = SUSER_SNAME()
  
  -- get job id
  select @job_id=job_id
  from msdb.dbo.sysjobs sj
  where sj.name='Check for Dealers Sent not Boarded on CSC'
  
  -- invalid job name then exit with an error
  if @job_id is null
      RAISERROR (N'Unknown job: %s.', 16, 1, 'Check for Dealers Sent not Boarded on CSC')
  
  -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
  declare @xp_results TABLE ( job_id                UNIQUEIDENTIFIER NOT NULL,
                              last_run_date         INT              NOT NULL,
                              last_run_time         INT              NOT NULL,
                              next_run_date         INT              NOT NULL,
                              next_run_time         INT              NOT NULL,
                              next_run_schedule_id  INT              NOT NULL,
                              requested_to_run      INT              NOT NULL, -- BOOL
                              request_source        INT              NOT NULL,
                              request_source_id     sysname          COLLATE database_default NULL,
                              running               INT              NOT NULL, -- BOOL
                              current_step          INT              NOT NULL,
                              current_retry_attempt INT              NOT NULL,
                              job_state             INT              NOT NULL)
  
  -- start the job
  declare @r as int
  exec @r = msdb..sp_start_job 'Check for Dealers Sent not Boarded on CSC'
  
  -- quit if unable to start
  if @r&lt;&gt;0
      RAISERROR (N'Could not start job: %s.', 16, 2, 'Check for Dealers Sent not Boarded on CSC')
  
  -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
  WAITFOR DELAY '0:0:01';
  set @seccount = 1
  
  -- check job run state
  insert into @xp_results
  execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
  
  set @running= (SELECT top 1 running from @xp_results)
  
  while @running&lt;&gt;0 and @seccount &lt; @maxseccount
  begin
      WAITFOR DELAY '0:0:01';
      set @seccount = @seccount + 1
  
      delete from @xp_results
  
      insert into @xp_results
      execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
  
      set @running= (SELECT top 1 running from @xp_results)
  end
  
  -- result: not ok (=1) if still running
  
  if @running &lt;&gt; 0 begin
      -- still running
      return 0
  end
  else begin
  
      -- did it finish ok ?
      set @run_status = 0
  
      select @run_status=run_status
      from msdb.dbo.sysjobhistory
      where job_id=@job_id
        and cast(run_date as bigint) * 1000000 + run_time &gt;= @start_job
  
      if @run_status=1
          return 1  --finished ok
      else  --error
          RAISERROR (N'job %s did not finish successfully.', 16, 2, 'Check for Dealers Sent not Boarded on CSC')
  
  end
  
  END TRY
  BEGIN CATCH
  
  DECLARE
      @ErrorMessage    NVARCHAR(4000),
      @ErrorNumber     INT,
      @ErrorSeverity   INT,
      @ErrorState      INT,
      @ErrorLine       INT,
      @ErrorProcedure  NVARCHAR(200);
  
  SELECT
      @ErrorNumber = ERROR_NUMBER(),
      @ErrorSeverity = ERROR_SEVERITY(),
      @ErrorState = ERROR_STATE(),
      @ErrorLine = ERROR_LINE(),
      @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
  
  SELECT @ErrorMessage =
      N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
          'Message: '+ ERROR_MESSAGE();
  
  RAISERROR
      (
      @ErrorMessage,
      @ErrorSeverity,
      1,
      @ErrorNumber,    -- original error number.
      @ErrorSeverity,  -- original error severity.
      @ErrorState,     -- original error state.
      @ErrorProcedure, -- original error procedure name.
      @ErrorLine       -- original error line number.
      );
  
  END CATCH
  
       end
  </code></pre>