SSIS DB in SQL Server - how to check contents of SSIS package?

Question

I have a SQL Agent job that runs certain SSIS packages. While I do know the location of those SSIS projects, I don't know which version of those packages is currently deployed in SQL Server.

For example, if I deployed an SSIS package from 'Project1' to SQL Server Integration Services Catalogs last month, but someone else changed that package and saved the changes to 'Project1', and did not deploy the changes, how would I be able to find out whether the current package is equivalent to what I have deployed?

Many thanks!

Answer 1

In the SQL Server Integration Services Catalog database, you can retrieve the project ID for the SSIS project you're interested in

SELECT project_id
FROM catalog.projects
WHERE project_name = 'Project1';

Once you have the project ID, you can query the [catalog].[object_versions] table to get all the information about the package version:

 SELECT package_name, version_major, version_minor, version_build, version_comments
    FROM catalog.object_versions
    WHERE project_id = <project_id>
      AND object_type = 20; -- 20 represents the package object type