SSIS Packages in SQL 2012/14

From SQL 2012 onwards, ssis packages can be deployed in project model. This requires creating a catalog and enabling ‘CLR’ integration. Once the packages are deployed in project model, they can be browsed in the ‘Integration Services Catalog’ in the management studio. Below code retrieves the packages along with the folder and packages present in the integration services catalog.

select as [FolderName], as[ProjectName], as [PackageName]
from [catalog].[folders] A
INNER JOIN [catalog].[projects] B ON A.folder_id=B.folder_id
INNER JOIN [catalog].[packages] C on C.project_id=B.project_id