December 9, 2015 at 9:18 am
I need to derive a list of all SSIS Pkg's by Name and CreatorName.
This link: https://msdn.microsoft.com/en-us/library/ms137749(v=sql.110).aspx mentions "Identification" which includes the CreatorName.
Any idea which Catalog table in SQL 2012 contains the CreatorName?
We're using SQL 2012 Enterprise. We're also deploying to the SSISDB in the Integration Services Catalog.
(I found a table in msdb called SSISDB.internal.packages .. but it does not contain the CreatorName)
December 14, 2015 at 6:44 am
this sufficed although it does not list the packages inside projects:
SELECT SUBSTRING(Name, 1, 45) as Name,
SUBSTRING(Deployed_By_Name, 1, 20) as Deployed_By_Name,
Last_Deployed_Time,
Created_Time
FROM [SSISDB].[catalog].[projects]
ORDER BY Name, Deployed_By_Name
March 13, 2018 at 3:01 pm
based on some ideas gained from
https://www.sqlservercentral.com/Forums/1324026/Searching-for-strings-in-SSIS-packages
the following query shows the inital part of what is stored in the packagedata column of msdb.sysssispackages table
SELECT NAME
,description
,SUSER_SNAME(ownersid)
, convert(varchar(max),convert(varbinary(max),packagedata)) as PkgData
--,*
FROM msdb.dbo.sysssispackages -- sql 2008
--WHERE SUSER_SNAME(ownersid) <> 'sa'
where name like 'test%'
Copying the output from PkgData shows the DTS:CreatorName="xxxx\yyyy" so you could parse the CreatorName from the packagedata column.
This may vary by SQL version and where you store the data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply