views:

386

answers:

3

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.

+1  A: 

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

StewNoble
+1  A: 
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

Nathan
+1  A: 

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.

Dayton Brown
This is simple, easy and integrates with whatever other progress reporting you do.
DaveE