In case you are interested to use T-sql to get the actual creator
information, you can try this.
**This
is good for 2008r2. 2012 is slightly different, I will try to adjust it.**
Using SELECT * FROM MSDB..sysssispackages to find the package
owner information will mislead you. This return actually who deply the package
into the server.
PackageName
|
createdate
|
ownersid
|
_del_EHS_MOBILEVENDING_x_EHSLicenseDataImport
|
01/29/13
|
EH\dbadminABC
|
I added a few extra step to get the actual package creator information.
PackageName
|
createdate
|
PackageCreator
|
_del_EHS_MOBILEVENDING_x_EHSLicenseDataImport
|
01/29/13
|
EH\ppavel
|
with ChildFolders
as
(
select
PARENT.parentfolderid,
PARENT.folderid,
PARENT.foldername,
cast('' as sysname) as RootFolder,
cast(PARENT.foldername as varchar(max)) as FullPath,
0 as
Lvl
from msdb.dbo.sysssispackagefolders
PARENT
where
PARENT.parentfolderid is null
UNION ALL
select
CHILD.parentfolderid,
CHILD.folderid,
CHILD.foldername,
case
ChildFolders.Lvl
when
0 then CHILD.foldername
else
ChildFolders.RootFolder
end as RootFolder,
cast(ChildFolders.FullPath
+ '/' + CHILD.foldername as varchar(max))
as
FullPath,
ChildFolders.Lvl
+ 1 as Lvl
from msdb.dbo.sysssispackagefolders
CHILD
inner
join ChildFolders on
ChildFolders.folderid = CHILD.parentfolderid
)
select RootFolder = F.RootFolder,
FullPath=
F.FullPath,
PackageName =
P.name,
PackageOwner = SUSER_SNAME(ownersid),
PackageDescription = P.description ,
P.packageformat,
P.packagetype,
P.vermajor,
P.verminor,
P.verbuild,
P.vercomments,
CreateDate =
p.createdate,
cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData
into #t
from ChildFolders F inner join msdb.dbo.sysssispackages
P on P.folderid
= F.folderid
order by F.FullPath asc, P.name asc;
select PackageName, createdate,
PackageCreator
= SUBSTRING(SUBSTRING(convert (nvarchar(max),
packagedata),
CHARINDEX('<DTS:Property
DTS:Name="CreatorName">',
convert (nvarchar(max), packagedata)),LEN(convert (nvarchar(max), packagedata))),
38,
CHARINDEX('</DTS:Property>', SUBSTRING(convert (nvarchar(max),
packagedata),
CHARINDEX('<DTS:Property DTS:Name="CreatorName">', convert (nvarchar(max),
packagedata)),LEN(convert (nvarchar(max),
packagedata)))) -38) from #t
drop table
#T
|
