October 23, 2008 at 5:56 am
Hello,
I have a SQL Server 2005 job that runs an SSIS package. When I run the job from Management Studio it runs successfully. I need to now incorporate this job into my stored proceudre. Once the job is initiated from the stored procedure, execution of the SP should stop until the job is done then the following lines of code in the SP should be executed. The package that is run loads data into a table that the stored procedure needs to operate on. How do I go about doing this in my stored procedure?
Thanks and Regards
October 23, 2008 at 7:46 am
Personally, I think it would be easier to incorporate the SP into the job or SSIS package than poll the job status. Any chance you can pull all the logic out of the SSIS package back into the stored procedure or is it pretty complicated?
It will take a little work, but you can use sp_help_job to get the current status of the job, then go into a loop where you wait for the status to go to idle before breaking out. Make sure you pause in the loop (use a WAITFOR) so that you don't constantly hit the server for status and spike the CPU.
Good luck!
Chad
October 23, 2008 at 7:52 am
set nocount on
declare @jobid uniqueidentifier
select @jobid = job_id from msdb..sysjobs_view where [name] = 'JobName'
CREATE TABLE #xp_results (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)
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
declare @execution_status int
set @execution_status = (select job_state from #xp_results where job_id = @jobid)
drop table #xp_results
if @execution_status = 1
RETURN
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply