question

BrianGlenn avatar image
BrianGlenn asked

How can I check SQL AGENT Job status in an precedence expression

I am running multiple tasks in a SQL Server Agent Job, and need to verify the job has ran and completed successfully before allowing the next task to begin. Is there a way to do this using an expression in the precedence constraint?
ssisconstraints
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
0 Likes 0 ·
JohnSterrett avatar image
JohnSterrett answered
Could you leverage built in features of SQL Agent like creating an additional step so the first step has to complete before the second one? Another option if you need only one step in your Job is based on condition you could tell the step to execute another SQL Agent Job. If this is helpful please mark the response as answered.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BrianGlenn avatar image
BrianGlenn answered
Created a query to return a value to a scalar. Use the scalar in a loop with a delay until the status is "Suceeded", and then allow the next job to start. Declare @MyStatus nvarchar(10) Set @MyStatus = (Select CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' -- In Progress END AS [LastRunStatus] FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN ( SELECT [job_id] , [run_date] , [run_time] , [run_status] , [run_duration] , [message] , ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0 ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1 Where sJOB.name Like 'Index Maintenance Job%') Thanks for your time and knowledge!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.