SSIS Job Ownership

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.

[codesyntax lang=”tsql”]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

--Display SSIS PackageOwners andJob Owners SQL2008

Select dts.[name]

,dts.[description]

,dts.createdate

,dts.ownersid

,p.name AsPackageOwner

,JOB.name asJobOwner

,SSISPackageType=

casedts.packagetype

when0then'default value'

When1Then'SQL Server Import and Export Wizard'

When2Then'DTS Designer in SQL Server 2000'

When3Then'SQL Server Replication'

When5Then'SSIS Designer'

When6Then'Maintenance Plan Designer or Wizard'

End

From msdb.dbo.sysssispackages dts

Left Join master.sys.server_principalsp

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

[/codesyntax]

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.

SQL Server is full of good stuff. There are plenty of features to be used. Plenty of applications to help it. And there is even plenty of metadata within SQL Server to help you better understand your data and the queries that are being run. It just so happens that a couple of clients requested[…]

Today we have another installment in what is known as TSQL Tuesday. This month we have an invitation and topic given to us by the infamous Kenneth Fisher ( blog | twitter). Today, the invitation is for us to share our stories on how we like to manage security. Or at least that is the[…]

Recently I wrote an article about Capturing Online Index Operations. In that article, I discussed a problem that I had encountered. Well, there were multiple problems. One was an issue with a vendor app that had some hidden module that was performing online index defrags that was causing corruption in a couple of indexes every[…]

One of the things that DBAs love to do is keep their servers running and healthy. A healthy server, after all, is your ticket to a stress free day and a full night’s sleep. Granted this not a guarantee but it sure helps make life easier. We are always looking for the big ticket items[…]

What a fun week we have tuned up for the folks in Las Vegas. It is the first full week of January and there is this huge convention going on near the strip. And as timing would have it, this week is also the perfect time to have our User Group meeting. What major conference[…]

Tis the season for TSQL Tuesday. Not only is it that season again, but it is also the Holiday season. During this season, many people start to think about all of the things for which they are thankful. Many may start to think about their families and friends. And many others will focus more of[…]

This past weekend I had the opportunity to go visit Washington DC. It was just the second time I got to stay in the Nation’s capitol for more than just a few hours. The previous opportunity came with last years event which I talked about here. Sadly, my time was far too limited this trip and[…]

I am about to set sail on a new venture with my next official whistle stop. This year has been plenty full of whistle stops and I plan on continuing. You can read (in full) about previous whistle stops and why they are called whistle stops here. Suffice it to say at this point that it all[…]