How to check the SSIS packages are connected to a server

Question

I am new to SSIS, I have few questions please help me.

1. How to check which Solution is connected to the server to run the packages, Because the package which the job was pulling is not working, I have run the package in the prod server, and which is working fine.
2. How to check Which ISPAC file picked from the server/

Thanks

Answer 1

This query will give you the package and job name of all packages running on the server.

;WITH CTE1 AS (
SELECT 
    J.job_id
    ,JobName = J.name
    ,JS.step_id, JS.step_name, JS.command
    ,StartIndex = 
        CASE 
            WHEN JS.command LIKE '/DTS%' OR JS.command LIKE '/SQL%' OR JS.command LIKE '/ISSERVER%' THEN CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1) --'
            WHEN JS.command LIKE '/SERVER%' THEN CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) + 1
            ELSE 0
        END
    ,EndIndex = 
        CASE 
            WHEN JS.command LIKE '/DTS%' OR JS.command LIKE '/SQL%'  OR JS.command LIKE '/ISSERVER%' 
                THEN  CHARINDEX('"',JS.command, CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1)) --'
                    - CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1) - 1 --'
            WHEN JS.command LIKE '/SERVER%' 
                THEN  CHARINDEX('"',command, CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) + 1)
                    - CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) - 1
            ELSE 0
        END
FROM msdb.dbo.sysjobsteps JS
INNER JOIN msdb.dbo.sysjobs J
    ON JS.job_id = J.job_id
WHERE JS.subsystem = 'SSIS'
 )    
   SELECT 
  C1.job_id
      , C1.JobName
        , C1.step_id
          , C1.step_name
           , PackageFolderPath = 
    CASE 
        WHEN C1.command LIKE '/DTS%' OR C1.command LIKE '/ISSERVER%' THEN 
         SUBSTRING(C1.command, C1.StartIndex, C1.EndIndex)
        WHEN C1.command LIKE '/SQL%' THEN '\MSDB' + SUBSTRING(C1.command, 
          C1.StartIndex, C1.EndIndex)
        WHEN C1.command LIKE '/SERVER%' THEN '\MSDB\' + SUBSTRING(C1.command, 
            C1.StartIndex, C1.EndIndex)
        ELSE NULL
    END
, C1.command
      FROM CTE1 C1
      ORDER BY C1.job_id, C1.step_id