List jobs, job steps and their SSIS packages and environments respectively.

list-agent-job-ssis-steps.sql

SELECT j.[name] AS Job,
       s.step_id AS Step,
       s.step_name AS [Step name],
       s.subsystem AS [Subsystem],
       s.[command],
       x2.p AS [SSIS path],
       env.environment_name AS [Environment name]
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS s ON j.job_id=s.job_id
OUTER APPLY (
    SELECT SUBSTRING(s.[command], NULLIF(CHARINDEX('/ISSERVER "\"\', s.[command]), 0)+14, LEN(s.[command])) AS p,
           SUBSTRING(s.[command], NULLIF(CHARINDEX('/FILE "\"', s.[command]), 0)+9, LEN(s.[command])) AS f,
           SUBSTRING(s.[command], NULLIF(CHARINDEX('/ENVREFERENCE ', s.[command]), 0)+14, LEN(s.[command])) AS e
    WHERE s.subsystem='SSIS'
    ) AS x1
OUTER APPLY (
    SELECT LEFT(ISNULL(x1.p, x1.f), NULLIF(CHARINDEX('\""', ISNULL(x1.p, x1.f)), 0)-1) AS p,
           LEFT(x1.e, NULLIF(CHARINDEX(' ', x1.e+' '), 0)-1) AS e
    ) AS x2
LEFT JOIN SSISDB.[catalog].environment_references AS env ON env.reference_id=TRY_CAST(x2.e AS int)
ORDER BY j.[name], s.step_id;