Are you looking to do this identification after the fact or as the jobs are running? Regardless, trying to determine how long is 'too long' is not always a simple task.
-craig
"You can never have too many knives" -- Logan Nine Fingers
I would to like to do this identification after the fact immediately after every load on a day-to-day basis so that i can collect the statistics for few days and observe the performance of the job.
The way I have achieved this is, by writing the start time of each job at the start of job and then keep on checking the elapsed time from this log file and if a job is running more than expected*, an alert is raised. Also, I am currently running the jobs using UNIX scripts, but then the initial implementation was done using before and after job routines.
*Have stored the average time or acceptable wait time for each job in another file, which can then act as the acceptable time limit for comparison.
EtlStats can get all the jobs stats for the last time a job was run. When it asks for job name then just put all in there and it will get every job's last run. It stores elapsed time in a table, so it is easy to sort by this. If you run daily then just get all the job runs the next morning.