T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kevin Chant (b|t) who has asked us to describe “Your Fantasy SQL Feature“. (This is also the first time I’ve made a blog post for T-SQL Tuesday.)
The Problem: As a junior DBA, part of my job involves checking job failures for 2-3 clients each day, which we have a script in-house for. Every once in a while, a job actually fails! So like any DBA who knows that you can go to the Job Activity Monitor under the SQL Agent to view details on job history, i do exactly that only to find it’s a job that ran an SSIS package and gave me this very informative error message:
That’s super helpful, thanks!
So now i have to:
- Check the step properties
- Look at the path for the SSIS package
- Drill down into the integration services catalogue
- clickity, clickity
- Wait which folder was it in?
- Go check the path again
- clickity clickity
- There’s 12 different packages with very similar names:
- Resize the window or scroll over to see which one i care about
- Finally, open the All Executions Report to get a very helpful error message:
Gee darnit, i’m out of coffee.
The Solution: Just put a button in the failure message/job history that does all that for you. Or a hyperlink. Whatever.
Kevin Hill (b|t) has made a script that reads the SSISDB tables, but often takes a while to run with only the existing Microsoft indexes. Especially if you are keeping crazy amounts of history…like the MS default 365 days, on a busy system.
/*Please work*/,
SQLDork
