CODEWITHSUNDEEP
sql-serverssis
Juan
Juan

Reputation: 1382

How to find connection of SSIS Package in a SQL Job Step

I have a list of jobs to run multiple ssis packages. I have a big list, I want to know if it is possible to run a tsql to msdb and get the information of the packages in the steps with the connection strings.

Upvotes: 1

Views: 10223

Answers (3)

user16448387
user16448387

Reputation: 41

Super old thread but the below code is slightly adapted and much more useful as it shows package level details.

select  
     prj.name                    as 'ProjectName'
    ,pa.name                     as 'SSISPackageName'
    ,op.parameter_name           as 'ParmaterName'
    ,op.design_default_value     as 'ConnectionString'
from    
     catalog.object_parameters   op
join catalog.projects            prj
  on op.project_id               = prj.project_id
join [catalog].[packages]        pa 
  on pa.project_id               = prj.project_id
where op.parameter_name          like '%ConnectionString%'

Upvotes: 4

VKarthik
VKarthik

Reputation: 1429

If your packages are stored in the SSISDB Catalog, then you would need to query on SSIDB database to get the actual connection strings. Here is what I would suggest, use the query that Rodrigo A has provided and tweak the column 'Command' further to get the packages name from it by using string functions and have the Step ID and Package Name as output. use the list of packages that you have obtained and put it in the IN clause of the following query and run it against SSISDB - 

select  prj.name                 as 'ProjectName'
        ,op.object_name          as 'SSISPackageName'
        ,op.parameter_name       as 'ParmaterName'
        ,op.design_default_value as 'ConnectionString'
from    catalog.object_parameters op
        join catalog.projects prj
            on op.project_id = prj.project_id
where   op.parameter_name like '%ConnectionString%'
        and op.object_name in (
        --put the package name list here
        );

You can then join on the result sets, to get the step Id, PackageName and the connection strings in that package.

Upvotes: 1

Rodrigo A.
Rodrigo A.

Reputation: 73

Run this query over the server you want to check:

SELECT [sJSTP].[step_id], Name, sJSTP.Command FROM [msdb].[dbo].[sysjobs] [sJOB]
  LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    ON [sJOB].[job_id] = [sJSTP].[job_id]
    AND [sJOB].[start_step_id] = [sJSTP].[step_id]
ORDER BY Name, step_id

Cheers!

Upvotes: 0

Related Questions