What table stores status of SSIS jobs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What table stores status of SSIS jobs

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-04-23 : 14:55:25

Hi
Could any one please let me know what table stores status of an SSIS Job , i know table sysjobhistory in MSDB db , which has a column run_status , but no where the status is 4 which means job is running , can i know any other table which shows the status of the job as running ? basically i need to create a job which alerts me when the job duration is more than 5hrs...

Any help is highly appreciated ,

Thank you

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-04-24 : 05:20:21

[code]
SELECT
*
FROM MSDB..SysJobHistory jobH
JOIN MSDB..SysJobs job
ON job.Job_Id = jobH.job_id
WHERE job.name = 'job_getdate'

ORDER BY run_date desc
[/code]

status 4 is "in progress" status.
so what you can do is calculate from previous step of the job how much time has elapsed and if time_elapsed is greater that 5 hours use
command:
[code]
EXEC dbo.sp_stop_job N'job_getdate'
[/code]

but i suggest that you do such event handling within SSIS package. one in edit mode of your dtsx file, click on tab "event handlers" and set it there or use it within dataflow as a separate variable to calculate time elapsed.

rds207
Posting Yak Master

198 Posts

Posted - 2010-04-26 : 18:04:22

where do i find column run_status as 4 , when i query sysjobhistory table i cannot find any job where run_status is 4 even though the jobs are running.......

quote:
Originally posted by slimt_slimt

SELECT
*
FROM MSDB..SysJobHistory jobH
JOIN MSDB..SysJobs job
ON job.Job_Id = jobH.job_id
WHERE job.name = 'job_getdate'

ORDER BY run_date desc

status 4 is "in progress" status.
so what you can do is calculate from previous step of the job how much time has elapsed and if time_elapsed is greater that 5 hours use
command:

EXEC dbo.sp_stop_job N'job_getdate'

but i suggest that you do such event handling within SSIS package. one in edit mode of your dtsx file, click on tab "event handlers" and set it there or use it within dataflow as a separate variable to calculate time elapsed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources