Sql Server 2005 SSIS/Agent - Query status of a job

Question

Is there a way to query the current status (executing, idle, etc) and the last result (successfull, failed, etc), and the last run time for a specific job name? The end result I am looking for is being able to display this information in an internal web application for various SSIS packages.

Answer 1

You should be able to find this information inMSDB - there are tables sysjobs, sysjobhistory and sysjobsteps which give the information that you are looking for

Answer 2

exec msdb.dbo.sp_help_job @job_name = 'TheJobName'

gives the information I want. So then I can just use a SqlDataReader to get the information. Note that this stored procedure returns multiple result sets.

The micrsoft documentation on this store procedure is http://msdn.microsoft.com/en-us/library/ms186722(SQL.90).aspx

Answer 3

Another solution I have used is to update a reference table with the current status. It's quick and easy and usually very easy to retrieve the values you need.

For example, as soon as a package kicks off, insert a record with date and time, package name, etc.