Find DTS and FTP Job Steps From msdb
I started a new full-time contract this month, which is always weird. Not having a full understanding of the project, access rights roadblocks, and unfamiliarity with the code base all team up to make days feel long and unproductive.
One of the things I needed to do is to find where data is regularly getting imported into a database. I came up with this query on msdb to show me scheduled tasks that run FTP commands or DTS packages and had been run during this calendar year.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT j.name AS job_name , j.description AS job_description , st.step_name AS step_name , st.command , st.last_run_duration , st.last_run_date , st.last_run_time FROM msdb.dbo.sysjobsteps st JOIN msdb.dbo.sysjobs j on st.job_id = j.job_id WHERE 1 = 1 AND (st.command LIKE '%ftp%' or st.command LIKE 'dtsrun%' ) AND st.last_run_date LIKE '2014%' AND j.enabled = 1 |
The items in the where clause are pretty much self-explainitory and can be changed to meet the need of any SQL detective. It’s one I’ll keep in my toolbox for a while, I suspect.
Aside: what’s with the 1 = 1 at the top of the WHERE clause?
If I’m doing a lot of commenting and uncommenting of conditions, I like to just start my WHEREs with 1 = 1. That way, I don’t have to worry about whether or not I need to include the AND when I comment something. I can just start dropping “–” at the start of each line and not think about it any more than that.