CODEWITHSUNDEEP
sql-serverssisssis-2012ssis-2008
New_Coder_AI
New_Coder_AI

Reputation: 11

How to check the SSIS packages are connected to a server

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

Upvotes: 0

Views: 239

Answers (1)

perry147
perry147

Reputation: 197

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

Upvotes: 0

Related Questions