And Now a Couple of Job Queries

By Lori Brown | Helpful Scripts

Apr 18

Hooray!!  We finally have our 70-764 and 70-765 certification exams done and can turn our attention back to blogging more regularly.  Those exams were pretty tough mostly because there was a lot of information that had to be memorized since there are always aspects of SQL that we don’t regularly use and there is no access to internet searches during the exam.  All of us (or maybe it’s just me) are by now so used to having access to SQL help and detail information on how to find things using the internet that it seems to me that exams that cover such a large subject like SQL will likely become either more general or even more specialized.  I just don’t know anyone who has managed to memorize all of the DMV’s with their columns and input parameters along with all of the SQL PowerShell commandlets with their input parameters that apply.  Okay….rant over and on to the fun stuff!

I was recently working with one of our developers on an upgrade project where we had to move a boatload of jobs to the new SQL instance, many of which call SSIS packages stored in msdb.  Since we are moving up to SQL 2016 in this particular project, we wanted to store packages in the SSIS Catalog to take advantage of it’s flexibility.  This meant that my developer needed a query that would give him some info on jobs that were actually being actively run and were candidates for move.  They also wanted to find the jobs that called SSIS packages so that they could focus their work in the right places.

Here is what I came up with.  Hey….since he liked it, I thought you might too.

I have provided a few ways to filter info if you need it.  The results are pretty straightforward…

You can run each query independently if you like.  As usual, feel free to add to these and make them your own.  But, most importantly, enjoy.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!