SSIS Job Ownership

Comments: 2 Comments
Published on: April 11, 2011

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.

-- 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.

2 Comments - Leave a comment
  1. Brad Schulz says:

    Hi Jason…

    An alternative to that nasty string is the following ON predicate:

    ON SJS.Command LIKE ‘%\’+dts.name+’ “%’

    –Brad

    • Jason Brimhall says:

      Thanks so much Brad. That makes a lot of sense and is easier on the eyes. I knew somebody out there (probably a bunch of somebodies) would have some better advice for that script.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
April 2011
M T W T F S S
« Mar   May »
 123
45678910
11121314151617
18192021222324
252627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, April 20, 2014