SSIS: List all SSIS packages stored in msdb database

(Original item written by https://gallery.technet.microsoft.com/scriptcenter/List-all-SSIS-packages-3b247394)

This Transact-SQL statements lists all SSIS packages stored in the msdb system database with detailed information.
It gives a brief overview which packages exists, who created them and which is the actual version of it.

Works with SQL Server 2005 and higher versions in all editions.
Requires SELECT permissions on the tables of the msdb database.

Link:
sysdtspackages90 http://msdn.microsoft.com/en-us/library/ms181582(v=SQL.90).aspx

SQL
-- List all SSIS packages stored in msdb database. 
SELECT PCK.name AS PackageName 
      ,PCK.[descriptionAS [Description] 
      ,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 
      ,PCK.vercomments AS VersionComment 
      ,DATALENGTH(PCK.packagedataAS PackageSize 
FROM msdb.dbo.sysssispackages AS PCK 
     INNER JOIN msdb.dbo.sysssispackages AS FLD 
         ON PCK.folderid = FLD.folderid 
     INNER JOIN sys.syslogins AS LG 
         ON PCK.ownersid = LG.sid 
ORDER BY PCK.name;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s