June 22, 2017 at 5:56 am
I've got an SSIS package in which I need to download a file from a third party website, then run a job on a local SQL Server to import the file, then move on to other things. I can't move on to other things until the job is complete and SSIS doesn't wait for a job to be complete when that job is kicked off with sp_start_job in an EXECUTE SQL task. It moves on immediately after successfully starting the job. So I came up with some code to delay and loop through checking the MSDB tables, but it's not working. It keeps running once and coming up blank because the job isn't recording itself into MSDB..sysjobhistory until after the first step is complete.
I could use some help troubleshooting this code so I can figure out how to monitor a job after it's immediately kicked off (and force the loop to continue through the loop). Yes, I know the fact that it's always reaching 8 (my nullable "break this to prevent infinite looping" option) is causing it to only run once. But if I get rid of that and for some reason the job doesn't even kick off like ever, I don't want this to just sit and churn doing nothing. So suggestions would be greatly appreciated.
DECLARE @JobStatus TINYINT = 9;
WAITFOR DELAY '000:00:45.000';
WHILE 1 = 1
BEGIN
WAITFOR DELAY '000:02:00.000';
SELECT @JobStatus = ISNULL((SELECT DISTINCT ISNULL(sjh.run_status,0) AS JobStatus
FROM msdb..sysjobs sj
INNER JOIN msdb..sysjobhistory sjh
ON sj.job_id = sjh.Job_id
INNER JOIN (SELECT sja.[name], sja.job_id, MAX(sjha.instance_id) as InstanceID
FROM msdb.dbo.sysjobs sja
INNER JOIN msdb.dbo.sysjobhistory sjha
ON sja.job_id = sjha.Job_id
WHERE sjha.step_id > 0
GROUP BY sja.[name], sja.job_id) LastRun
ON sj.job_id = LastRun.job_id
AND sjh.instance_id = LastRun.InstanceID
INNER JOIN msdb..sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE sj.Name = 'MyJob' AND sjh.run_status = 1 --Job has finished running
AND sjh.Run_Date = CONVERT(INT,SUBSTRING(CONVERT(CHAR(10),GETDATE(),101),7,4)
+ SUBSTRING(CONVERT(CHAR(10),GETDATE(),101),1,2)
+ SUBSTRING(CONVERT(CHAR(10),GETDATE(),101),4,2) )
AND sjs.step_id = 2), 8); --ensures all steps have run in the job
IF @JobStatus IN (0,1,3) --Failed, Succeeded or Cancelled
BEGIN
BREAK;
END;
ELSE IF @JobStatus = 2 --In progress
BEGIN
CONTINUE;
END;
ELSE IF @JobStatus = 8 --made up value to counter NULL
BEGIN
BREAK;
END;
END;
June 22, 2017 at 6:23 am
This isn't editing your code, but I've had a similar problem with an SSIS task that needs to be called from the our website. When the task is kicked off, the procedure completes, however, SSIS isn't finished yet.
I effectively set up a polling process that checks pretty frequently if the job has completed, and is part of the task that actually runs the SSIS in SSISDB. Although not answering your question, per say, not sure if this would help at all instead? This is my actual SP, so if it does, you'd need to alter it to suit your needs (@Branch, @Polref and @APM are all parameters for the SSIS package).
ALTER PROC web.CopyOADocs_sp @Branch sql_variant, @Polref sql_variant, @APM bit AS
DECLARE @execution_id bigint;
EXEC SSISDB.catalog.create_execution @package_name = N'Get Reference OA Docs.dtsx', @execution_id = @execution_id OUTPUT, @folder_name = N'SSIS Packages', @project_name = N'Web', @use32bitruntime = False, @reference_id = NULL;
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id, @object_type = 30, @parameter_name = N'Branch', @parameter_value = @Branch;
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id, @object_type = 30, @parameter_name = N'Quote', @parameter_value = @APM;
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id, @object_type = 30, @parameter_name = N'Reference', @parameter_value =@Polref;
DECLARE @var3 smallint = 1;
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id, @object_type = 50, @parameter_name = N'LOGGING_LEVEL', @parameter_value = @var3;
EXEC SSISDB.catalog.start_execution @execution_id;
DECLARE @execution_result int,
@i int = 1;
SELECT @execution_result = EI.status
FROM SSISDB.internal.execution_info EI
WHERE EI.execution_id = @execution_id;
WHILE @execution_result NOT IN (3,4,6,7,9) AND @i <= 25 BEGIN
WAITFOR Delay '00:00:00.100';--My task is pretty quick, but you have a 2 minute delay on yours, so you probably want to change this.
SELECT @execution_result = EI.status
FROM SSISDB.internal.execution_info EI
WHERE EI.execution_id = @execution_id;
PRINT 'Loop ' + CAST(@i AS varchar(2));
SET @i = @i + 1;
END
SELECT CASE WHEN @execution_result IN (7,9) THEN 'Success'
WHEN @execution_result IN (3,4,6) THEN 'Failed'
ELSE 'Timed Out'
END AS ExecutionResult;
--You might not need this bit, but I do it for logging in the event of website errors.
INSERT INTO web.SSISLog_tbl (Branch, Polref, APM, ReturnCode, ReturnString)
SELECT CAST(@Branch AS nvarchar(2)),
CAST(@Polref AS nvarchar(10)),
@APM,
@execution_result,
CASE WHEN @execution_result IN (7,9) THEN 'Success'
WHEN @execution_result IN (3,4,6) THEN 'Failed'
ELSE 'Timed Out'
END AS ExecutionResult;
GO
Aware the alignment is a little off, sorry. SSC still hates me pasting. 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 22, 2017 at 7:29 am
Thom, I have not read your code in detail, but doesn't running a package execution in SYNCHRONOUS mode obviate the need for this polling?
June 22, 2017 at 7:41 am
Phil Parkin - Thursday, June 22, 2017 7:29 AMThom, I have not read your code in detail, but doesn't running a package execution in SYNCHRONOUS mode obviate the need for this polling?
Doesn't synchronous mode only apply to transformations (https://docs.microsoft.com/en-us/sql/integration-services/understanding-synchronous-and-asynchronous-transformations)? Synchronous mode wouldn't help me anyway. Once the sp_start_job returns a success (or failure) the package would move onto the next step anyway because the proc has completed even if the job hasn't.
Thom, I'll look at your code more closely, but at first glance it appears package related rather than job related. The job I'm running kicks off a vendor .bat file which kicks off a .cmd file, not another package. So checking the SSIS Catalog (which we don't even use for any of our stuff) wouldn't help much. Still, I'll take a closer look at it.
June 22, 2017 at 7:46 am
Phil Parkin - Thursday, June 22, 2017 7:29 AMThom, I have not read your code in detail, but doesn't running a package execution in SYNCHRONOUS mode obviate the need for this polling?
I honestly didn't realise that was an option... I'd done a decent amount of Googling as well. Clearly I didn't use the right keywords. Thanks Phil 😀
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 22, 2017 at 8:03 am
Brandie Tarvin - Thursday, June 22, 2017 7:41 AMPhil Parkin - Thursday, June 22, 2017 7:29 AMThom, I have not read your code in detail, but doesn't running a package execution in SYNCHRONOUS mode obviate the need for this polling?Doesn't synchronous mode only apply to transformations (https://docs.microsoft.com/en-us/sql/integration-services/understanding-synchronous-and-asynchronous-transformations)? Synchronous mode wouldn't help me anyway. Once the sp_start_job returns a success (or failure) the package would move onto the next step anyway because the proc has completed even if the job hasn't.
Thom, I'll look at your code more closely, but at first glance it appears package related rather than job related. The job I'm running kicks off a vendor .bat file which kicks off a .cmd file, not another package. So checking the SSIS Catalog (which we don't even use for any of our stuff) wouldn't help much. Still, I'll take a closer look at it.
Being able to execute a package in synchronous mode depends on the package being deployed to SSISDB, so I don't think it's of any use to you at this point.
The whole point about using sync mode is that the proc does not complete until package execution has completed.
The link you provided refers to the behaviour of components within an SSIS package and not to the execution mode (please check out the link I provided for more details).
June 27, 2017 at 6:06 am
Phil Parkin - Thursday, June 22, 2017 8:03 AMBeing able to execute a package in synchronous mode depends on the package being deployed to SSISDB, so I don't think it's of any use to you at this point.
The whole point about using sync mode is that the proc does not complete until package execution has completed.
The link you provided refers to the behaviour of components within an SSIS package and not to the execution mode (please check out the link I provided for more details).
I was checking out the link you provided. But the only references I could find to SynchronousInputID was in Data Flow tasks and not on Control Flow Tasks. Believe me, I looked all over my package for that property.
As it is, I apparently have to write T-SQL to do a double-check. One in sysjobactivity to find a line for the job being called "today" and then sysjobhistory after that to see when it finishes writing. I also had to increase my initial delay and change when my NULL value is being called to prevent the loop from breaking too early.
I'm running the package again today (third party has a new file for me to test finally) and will post my new code later after I've tested it again.
June 27, 2017 at 7:15 am
If I cut & paste some bits from the article, and add a few comments, maybe it will clarify things for you.
But my first comment is this: this concept has nothing to do with a specific SSIS package. Instead, it is related to how a package is executed.
DECLARE @execution_id BIGINT;
--Create an execution and save the execution Id
EXEC SSISDB.catalog.create_execution
@package_name = N'Child1.dtsx'
, @execution_id = @execution_id OUTPUT
, @folder_name = N'FrameworkTestFolder'
, @project_name = N'FrameworkRestartabilityTest'
, @use32bitruntime = False
, @reference_id = NULL;
DECLARE @synchronized BIT = 1;
--Tell the execution to run in synchronized mode
EXEC SSISDB.catalog.set_execution_parameter_value
@execution_id
, @object_type = 50
, @parameter_name = N'SYNCHRONIZED'
, @parameter_value = @synchronized;
--Start the execution
EXEC SSISDB.catalog.start_execution @execution_id;
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply