I was strolling along one day when I saw somebody asking how to find out who owns a maintenance plan.  That evolved into finding out who owns the the job associated with the maintenance plan.  All of this in SQL 2005 and SQL 2008.

Well, we were stumped for a bit trying to figure the link between the job tables in the msdb database and the ssis table in the same database.  Linking the two together is not very obvious and we struggled with it for a bit.  After some research and trying this that and the other, I was able to come up with the below script.

Code block

-- Display SSIS Package Owners and Job Owners SQL 2008
 SELECT dts.[name]
        ,dts.[description]
        ,dts.createdate
        ,dts.ownersid
        ,p.name AS PackageOwner
        ,JOB.name AS JobOwner
        ,SSISPackageType =
CASE dts.packagetype
WHEN 0 THEN 'default value'
WHEN 1 THEN 'SQL Server Import and Export Wizard'
WHEN 2 THEN 'DTS Designer in SQL Server 2000'
WHEN 3 THEN 'SQL Server Replication'
WHEN 5 THEN 'SSIS Designer'
WHEN 6 THEN 'Maintenance Plan Designer or Wizard'
END
   FROM msdb.dbo.sysssispackages       dts
LEFT Join master.sys.server_principals   p
ON p.sid = dts.ownersid
LEFT Outer Join msdb.dbo.sysjobsteps SJS
ON dts.name = SUBSTRING(SJS.command,CHARINDEX('\',sjs.command)+1,charindex('"',sjs.command,CHARINDEX('\',sjs.command))-CHARINDEX('\',sjs.command)-1)
And SJS.subsystem = 'ssis'
Left Outer Join msdb.dbo.sysjobs SJ
On SJS.job_id = SJ.job_id
Left Outer Join master.sys.server_principals JOB
On JOB.sid = SJ.owner_sid
Go

This script is set to work out of the gate with SQL 2008.  Should you want it to work with SQL 2005 the change is simple.  Change the sysssispackages table to sysdtspackages90.  As you can see, the query joins the SSIS table to the jobsteps table with a pretty nasty string extraction.  There are other ways of extracting this information (I’m sure of it).  This works quite well for what it is intended.

Using this script, you can find out the jobowner, the packageowner, and the packagetype.  This is pretty good information to have on hand if you have several ssis packages that are stored in msdb and are run from a job.  One thing this script does not yet handle is if the SSIS file is stored on the file system.  Note that I only coded it so far to work with files stored in SQL.  When looking in the jobsteps table, you can tell the difference quickly by seeing that those stored in msdb have a /SQL at the beginning of the command string.  Those in the filesystem have a /FILE en lieu of that /SQL.

In a future revision I will work on parsing the package name out of that string that represents those stored in the file system.  And despite that nasty join, this runs quickly on my systems.  I am open to suggestions or other solutions that can provide this kind of insight.