Search application name in SSIS job steps and SSIS packages in the catalog

search-ssis-catalog.sql

USE SSISDB;
GO

DECLARE @SearchValue NVARCHAR(250) = REPLACE('%application%', '_', '[_]');

-- A. Search inside Project and Package Parameters (Fixed Column Name)
SELECT 
    f.name AS [Folder Name],
    p.name AS [Project Name],
    o.object_name AS [Package Name (If Project Param, shows Project Name)],
    o.object_type AS [Object Type Code], -- 20 = Project, 30 = Package
    o.parameter_name AS [Parameter Name],
    o.data_type AS [Data Type],
    o.design_default_value AS [Design Default Value]
FROM catalog.object_parameters o
INNER JOIN catalog.projects p ON o.project_id = p.project_id
INNER JOIN catalog.folders f ON p.folder_id = f.folder_id
WHERE CAST(o.design_default_value AS NVARCHAR(MAX)) LIKE @SearchValue
   OR o.parameter_name LIKE '%Application%';

-- B. Search inside Environment Variables 
SELECT 
    f.name AS [Folder Name],
    e.name AS [Environment Name],
    v.name AS [Variable Name],
    v.type AS [Data Type],
    v.value AS [Variable Value]
FROM catalog.environment_variables v
INNER JOIN catalog.environments e ON v.environment_id = e.environment_id
INNER JOIN catalog.folders f ON e.folder_id = f.folder_id
WHERE CAST(v.value AS NVARCHAR(MAX)) LIKE @SearchValue
   OR v.name LIKE '%Application%';

search-ssis-job-steps.sql

DECLARE @SearchTerm NVARCHAR(250) = REPLACE('%application%', '_', '[_]');
PRINT @SearchTerm
SELECT 
    j.name AS [Job Name],
    s.step_id AS [Step ID],
    s.step_name AS [Step Name],
    s.subsystem AS [Subsystem Type],
    s.command AS [Job Step Command]
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
WHERE s.subsystem = 'SSIS'
  AND s.command LIKE @SearchTerm;
GO