I am trying to execute a SQL Server 2008 job using an execute SQL task in SSIS Package.
I used EXEC msdb.dbo.sp_start_job N'MORNING - iflagship_DataLoad_FULL';
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.
Any ideas how this can be possible ?
The below code executes the job but returns success right away even when the job is still running.
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<>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<>0 and @seccount < @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 <> 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 >= @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
