Sql Server 2005 SSIS/Agent – Query status of a job
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.
3 Solutions collect form web for “Sql Server 2005 SSIS/Agent – Query status of a job”
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
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
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.