Have Geek Brain

Sql Server 2005 SSIS / Agent - job request status

Is there a way to query the current status (in progress, idle, etc.) and the last result (successful, unsuccessful, etc.) and the last execution time for a specific job name? The end result I'm looking for can display this information in an internal web application for various SSIS packages.

+1


a source to share


3 answers 


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

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



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

+1


a source


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



+1


a source


Another solution I have used is to update the reference table with the current status. This is quick and easy and usually very easy to get the values ​​you want.

For example, once the package starts, insert an entry with date and time, package name, etc.

+1


a source






More articles:


All Articles