How to list all SSIS packages on the Sql Server 2008 using T-SQL
12,463
This query works for Sql Server 2008. The main difference is to use msdb.dbo.sysssispackages
and use left joins since some packages may have no owner or associated folder.
SELECT PCK.name AS PackageName
,PCK.[description] AS [Description]
,FLD.foldername AS FolderName
,CASE PCK.packagetype
WHEN 0 THEN 'Default client'
WHEN 1 THEN 'I/O Wizard'
WHEN 2 THEN 'DTS Designer'
WHEN 3 THEN 'Replication'
WHEN 5 THEN 'SSIS Designer'
WHEN 6 THEN 'Maintenance Plan'
ELSE 'Unknown' END AS PackageTye
,LG.name AS OwnerName
,PCK.isencrypted AS IsEncrypted
,PCK.createdate AS CreateDate
,CONVERT(varchar(10), vermajor)
+ '.' + CONVERT(varchar(10), verminor)
+ '.' + CONVERT(varchar(10), verbuild) AS Version
,PCK.vercomments AS VersionComment
,DATALENGTH(PCK.packagedata) AS PackageSize
FROM msdb.dbo.sysssispackages AS PCK
LEFT JOIN msdb.dbo.sysssispackagefolders AS FLD
ON PCK.folderid = FLD.folderid
LEFT JOIN sys.syslogins AS LG
ON PCK.ownersid = LG.sid
ORDER BY PCK.name;
Author by
Vadim K.
Updated on June 05, 2022Comments
-
Vadim K. almost 2 years
I have no access to connect to the SSIS subsystem via SQL Management Studio, so I was looking for a way to list all the SSIS packages via t-sql. I found the following query for Sql Server 2005, but it was not working for 2008:
-- List all SSIS packages stored in msdb database. SELECT PCK.name AS PackageName ,PCK.[description] AS [Description] ,FLD.foldername AS FolderName ,CASE PCK.packagetype WHEN 0 THEN 'Default client' WHEN 1 THEN 'I/O Wizard' WHEN 2 THEN 'DTS Designer' WHEN 3 THEN 'Replication' WHEN 5 THEN 'SSIS Designer' WHEN 6 THEN 'Maintenance Plan' ELSE 'Unknown' END AS PackageTye ,LG.name AS OwnerName ,PCK.isencrypted AS IsEncrypted ,PCK.createdate AS CreateDate ,CONVERT(varchar(10), vermajor) + '.' + CONVERT(varchar(10), verminor) + '.' + CONVERT(varchar(10), verbuild) AS Version ,PCK.vercomments AS VersionComment ,DATALENGTH(PCK.packagedata) AS PackageSize FROM msdb.dbo.sysdtspackages90 AS PCK INNER JOIN msdb.dbo.sysdtspackagefolders90 AS FLD ON PCK.folderid = FLD.folderid INNER JOIN sys.syslogins AS LG ON PCK.ownersid = LG.sid ORDER BY PCK.name;
After a little bit of research I figured out a query for 2008, so I'd like to share. See the answer below.