June 20, 2011 at 3:47 am
hi All,
Just need a steer on this ..what is best way to do this or has somebody out there already done it
i was thinking of calling a sp that calls its ?
if that is the way... then I assume the ssis package waits there till until it finishes
how do you notify to the package if it has failed or been successful
many thx....
June 20, 2011 at 4:38 am
robinrai3 (6/20/2011)
hi All,Just need a steer on this ..what is best way to do this or has somebody out there already done it
i was thinking of calling a sp that calls its ?
if that is the way... then I assume the ssis package waits there till until it finishes
how do you notify to the package if it has failed or been successful
many thx....
Maybe you could call sp_start_job in an Execute SQL task? This returns 0 or 1 depending on success/failure which can easily be assigned to an SSIS variable, which can then be used on subsequent precedence constraints to control the logical flow through the package.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 20, 2011 at 5:06 am
Phil,
many thx, I'll give it a go ...just wanted to know i wasn't going totally off piste on this by doing it this way...
June 20, 2011 at 5:13 am
Not at all - feel free to post back with findings/issues.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 20, 2011 at 6:17 am
robinrai3 (6/20/2011)
... then I assume the ssis package waits there till until it finisheshow do you notify to the package if it has failed or been successful
Phil provided the proc to start jobs, however it sounds like you want the SSIS package to wait until the job completes before proceeding after the proc call. Unfortunately sp_start_job does not work that way and the 0/1 return code it provides only signifies whether the job successfully started or stopped, not whether it failed or successfully completed.
Here is a connect suggestion to add "synchronous" functionality to the proc which was Closed as 'Won't Fix': http://connect.microsoft.com/SQLServer/feedback/details/434754/add-synchronous-wait-option-to-sp-start-job
In order to wait for the job to complete you may want to code a T-SQL wrapper around sp_start_job or a loop into your SSIS package that would check on the job's status and only proceed when the job completed.
Many people have needed this so you may find something that works for you on the net. Here is a Google search that can get you started:
Google: sp_start_job wait for job to finish
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 6:52 am
Great input. Makes my post only 50% correct & taught me something in the process, thanks.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 21, 2011 at 9:12 am
hi All
FYI... created an sp that you immediately call after you set yr job off.
it returns 0 or -1 on whether the job was successful or failed.
It basically waits in an endless loop while the job runs and then gets the job outcome
returning 0 or -1
Thx for all yr help guys
============================================================
/*************************************
declare @retcode int
exec @retcode = usp_wait_while_job_runs 'IRIS Import Personal Main XLS'
select @retcode 'run outcome'
*************************************/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_wait_while_job_runs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_wait_while_job_runs]
go
CREATE PROCEDURE usp_wait_while_job_runs
@jobname varchar(255)
AS
DECLARE @IsRunning int, @run_outcome int
SET @IsRunning = 1
WHILE (@IsRunning = 1)
BEGIN
-- Truncate Temporary table if it already exists
IF object_id('tempdb..#tempRunningJobs') IS NOT NULL
BEGIN
TRUNCATE TABLE #tempRunningJobs
END
ELSE
BEGIN
CREATE TABLE #tempRunningJobs
(
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
END
INSERT INTO #tempRunningJobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage
SET @IsRunning = 0
IF EXISTS ( SELECT 1
FROM
msdb..sysjobs a JOIN #tempRunningJobs b
ON a.job_id = b.job_id
WHERE a.name = @jobName
AND Running = 1
)
BEGIN
SET @IsRunning = 1
END
Print convert(varchar,getdate(),113)
WAITFOR DELAY '00:00:01'
END
-- Get the run outcome at end of job
SELECT @run_outcome = jh.run_status
FROM msdb..sysjobhistory jh join msdb..sysjobs j
on jh.job_id=j.job_id and j.name = @jobname
WHERE jh.Instance_id = (SELECT max(jh.Instance_id)
FROM msdb..sysjobhistory jh join msdb..sysjobs j
ON jh.job_id=j.job_id and j.name = @jobname)
IF @run_outcome = 1
return 0
ELSE
return -1
====================================================================
August 1, 2011 at 3:11 am
Ive incorporated your excellent stored procedure in to my SSIS package to check for completion of the job. Works great when I run it in debug mode. Reverts to asychronous when running my deployed package via a step in a sql agent job. I suspect it may be due to the print statement. Any idea what I could do to get it to work this way please?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply