I was recently tasked with moving / ugrading SSIS packages from one server to another and I was instructed "to only move the packages that are executed by jobs".
This script joins three tables; sysjobs, sysjobsteps, and sysssispackages. It produces a list showing package name, job name, package folder name, and the job command. It is written for packages stored in msdb, but could easily be adapted for an SSIS package store. Additional columns could also be added.
use msdb
go
SELECT sp.[name] as packagename,
sf.foldername,
sj.jobname,
sj.step_id,
sj.command
FROM sysssispackages sp
JOIN sysssispackagefolders sf ON sp.folderid = sf.folderid
JOIN (SELECT sj.[name] AS jobname,
sjs.step_id,
RIGHT(LEFT(REPLACE(REPLACE(sjs.command,'"', ''), '\ /', ' /'), LEN(REPLACE(REPLACE(sjs.command,'"', ''), '\ /', ' /')) - (LEN(REPLACE(REPLACE(sjs.command,'"', ''), '\ /', ' /')) - CHARINDEX( '/', REPLACE(REPLACE(sjs.command,'"', ''), '\ /', ' /'), 5)) - 2),
CHARINDEX('\', REVERSE(LEFT(REPLACE(REPLACE(sjs.command,'"', ''), '\ /', ' /'), LEN(REPLACE(REPLACE(sjs.command,'"', ''), '\ /', ' /')) - (LEN(REPLACE(REPLACE(sjs.command,'"', ''), '\ /', ' /')) - CHARINDEX( '/', REPLACE(REPLACE(sjs.command,'"', ''), '\ /', ' /'), 5)) - 2))) - 1) AS package_name,
sjs.command
FROM sysjobs sj
JOIN sysjobsteps sjs on sj.job_id = sjs.job_id
WHERE sjs.command LIKE '/DTS%' OR sjs.command LIKE '/SQL%') sj ON sp.[name] = sj.package_name
ORDER BY sf.foldername, sp.[name], sj.step_id 
The provided SSIS package contains a single script task that will script all SQL Server 2000/2005/2008 Agent Jobs into individual files.
2010-07-06
12,765 reads
Proxy accounts are a useful tool to enable teams to work independently allow users who do not have administrative access to SQL Server to run jobs.
2010-05-31
2,735 reads
In this article, learn how to use the SQL Server agent proxy to run an SSIS Package under whatever account you choose.
2010-03-25
57,023 reads
Checking for SQL Server Agent jobs and their status is part of your daily task as a DBA. How do we use Windows PowerShell to check for SQL Server Agent jobs?
2009-07-28
3,655 reads
In this tip I provide a script with a few parameters that need to be setup to configure and turn on both of these features.
2009-05-11
4,009 reads