SSIS 2012 -How To Get List of SSIS Packages With Folder Names From MSDB

Scenario:

A quick question sometime comes in our minds, How can I get the list of SSIS Packages those I have deployed to SQL Server with Folder Names?

Solution:

SQL Server 2012, provide two system tables in MSDB database those can be used to answer our question.

1.  [dbo].[sysssispackages]

2.  [dbo].[sysssispackagefolders]

Dbo.sysssispackages table will provide information about SSIS Package such as name, description,package createdate, foloderid (so we can join with sysssispackagefolders to get folder name) and other columns.

Dbo.sysssispackagefolder will provide us the folder name, folderid and parentfolderid. We can join above tables and get Packages with Folder information.

SELECT f.foldername            AS FolderName,

       p.name                  AS PackageName,

       p.description           AS [Description],

       P.createdate            AS PackageCreateDate,

       Suser_sname(p.ownersid) AS OwnerName,

       CASE

         WHEN p.isencrypted = 0 THEN 'N'

         ELSE 'Y'

       END                     AS [IsEncrypted]

FROM   [dbo].[sysssispackages] p

       INNER JOIN [dbo].[sysssispackagefolders] f

               ON p.folderid = f.folderid