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



8 comments:

  1. The list of SSIS can be very easily extracted anyways to find out how important and broader it aspects can be.

    SSIS Postgresql Read

    ReplyDelete
  2. very nice blog,Thank you for sharing this awesome blog.
    keep updating more blogs.

    Servicenow Training

    ReplyDelete
  3. Great Article,Thank you for sharing this great article with us.

    ServiceNow Training

    ReplyDelete
  4. NCERT 4th Class new Syllabus 2023 is Recently Released by National Council of Educational Research and Training (NCERT) is an Autonomous Organisation set up in 1961 by the Government of India to Assist and Advise the Central and State Governments on Policies and Programmes for Qualitative Improvement in School Education, NCERT 4th Class Syllabus 2023 Students are Advised to Thoroughly go Through the NCERT Class 4th Syllabus before they Start Studying for the new Session 2023.NCERT 4th Class new Syllabus 2023 is Recently Released by National Council of Educational Research and Training (NCERT) is an Autonomous Organisation set up in 1961 by the Government of India to Assist and Advise the Central and State Governments on Policies and Programmes for Qualitative Improvement in School Education.

    ReplyDelete
  5. We offer specialized packaging for hazardous materials in compliance with safety regulations.
    Automotive Packaging

    ReplyDelete