How to Query Integration Services Catalog

Question

Problem: Identify all SSIS packages that connect to a specific database table on SQL Server.

Details: There are almost 100 packages deployed to the server, most packages are huge in size, so it will be difficult to go through them manually with a high degree of accuracy.

Is there a fast and automated way to do this?

Potential solution using SQL Server:

1. Query the Integration Services Catalog and retrieve the *.dtsx package
2. Load the package data into an XML column/data type
3. Parse/query the package for any reference to the specific database table

Perhaps a C# application may do the same?

I greatly appreciate any assistance that can be afforded.

Answer 1

Credit goes to Ms SQL Girl: https://www.mssqlgirl.com/editing-published-ssis-package-in-sql-server-2012/#comment-8510

USE SSISDB

SELECT pr.name AS [ProjectName]
    , pr.description AS [ProjectDescription]
    , pr.last_deployed_time AS [ProjectLastValidated]
    , pr.validation_status AS [ProjectValidationStatus]
    , op.object_name AS [PackageName]
    , op.design_default_value AS [DefaultConnectionString]
FROM [internal].[object_parameters] op
INNER JOIN [internal].[projects] pr
ON pr.project_id = op.project_id
AND pr.object_version_lsn = op.project_version_lsn
WHERE op.parameter_name LIKE '%.ConnectionString'

Update: Updated the correct weblink. Thanks to Rob S.