How To Query Integration Services Catalog

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

Solution 1:

Credit goes to Ms SQL Girl: http://www.mssqlgirl.com/editing-published-ssis-package-in-sql-server-2012.html

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'

Solution 2:

I would open the Visual Studio project containing all these packages (using VS), and then use the VS "Find in Files" function to search for the table name. It is quite quick, and using the "Match whole word" search should pop up just what you want.

If you don't already have a Visual Studio project/solution containing these packages, you probably should download them and build one anyway, so they can be supported/maintained.