CODEWITHSUNDEEP
sql-serversql-server-2005ssis
Nathan
Nathan

Reputation: 10764

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.

Upvotes: 1

Views: 1885

Answers (3)

Dayton Brown
Dayton Brown

Reputation: 1240

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.

Upvotes: 1

Nathan
Nathan

Reputation: 10764

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

Upvotes: 1

StewNoble
StewNoble

Reputation: 51

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

Upvotes: 1

Related Questions