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.

  • Auto Increment a non-identity Column in sql-server
  • How to get the stored procedure result using perl?
  • Finding the height of the B-Tree of a table in SQL Server
  • Is querying on views slower than doing one query?
  • How to make CREATE OR REPLACE VIEW work in SQL Server?
  • Reset SQL Server execution plan
  • identity from sql insert via jdbctemplate
  • Does the order of tables referenced in the ON clause of the JOIN matter?
  • ASP.NET: connecting to a sql server database
  • How to determine size property for stored procedure output parameters in C# data access layer
  • Which join syntax is better?
  • PIVOTing variable number of rows to columns
  • 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
    http://msdn.microsoft.com/en-us/library/ms186722(SQL.90).aspx

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.