The following view will allow you to view the entire SSISDB structure easily, which is useful particularly when confirming the presence of certain folders, projects and packages from an SSIS Execute SQL Task.

Make sure you’re connected to the SSISDB database first before you run this code.

IF OBJECT_ID(N'catalog.structure', N'V') IS NOT NULL
  DROP VIEW catalog.structure;
GO
 
CREATE VIEW catalog.structure
  AS
  SELECT
         F.folder_id
       , F.name AS [folder_name]
       , PR.project_id
       , PR.name AS [project_name]
       , PA.package_id
       , PA.name AS [package_name]
       , CASE
         WHEN PA.version_major IS NOT NULL THEN
           CONCAT(N'v',PA.version_major,N'.',PA.version_minor,N'.',PA.version_build)
         ELSE
           NULL
         END AS [package_version]
  FROM catalog.folders AS F
    LEFT OUTER JOIN catalog.projects AS PR
      ON F.folder_id = PR.folder_id
    LEFT OUTER JOIN catalog.packages AS PA
      ON PR.project_id = PA.project_id;
GO
 
SELECT *
FROM catalog.structure;
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