Saturday, 22 January 2011

Time Job Last Ran

I recently had a request from a client to allow him to show on his reports when his datamart had last been updated by an SSIS package that was run on a schedule.

There are quite a few scripts to perform this task on the web, I felt that they are all over complicated and hard for a semi-technical person to follow modify should their needs change. (I like to leave my clients with code that they can understand and trust that they will want me back because I’m good at what I do rather than because I’m the only person that can understand the code I left them with)  J

The following short scrip is what I came up with to meet this requirement. As you can see, it only accesses 3 tables in the msdb database and can be followed by anyone with a basic understanding of T-SQL.

There are a number of other fields in these tables that might add value to your particular project, I’ll leave it to you to explore them and see what works for you.

declare @Job_Name varchar(8000)
set @Job_Name = ''            -- insert your job name here

select            -- collect the history for the required job as Job_Name
      ,a.start_execution_date as date_last_ran
      ,(    select MAX(a1.next_scheduled_run_date)
            from msdb.dbo.sysjobactivity a1
            where a1.job_id = j.job_id
      )     -- and the time it is next sceduled to run
      msdb.dbo.sysjobs j
      inner join msdb.dbo.sysjobactivity a on j.job_id = a.job_id
      inner join msdb.dbo.sysjobhistory h on a.job_history_id = h.instance_id
            and h.run_status = 1    -- limit to only succesful runs
where = @Job_Name

While it runs against table sin msdb you can happily put it anywhere, just so long as the user it runs under has read rights on the 3 tables. (I wrapped it in a stored procedure and put it in his datamart)

Naturally this comes with the standard disclaimers that apply to any code you might find for free on the web.

No comments:

Post a Comment