ssis packages and connection manager inputs

Question

HI Expert,

How can i get ssis packages and connection manager list from ssis db or any export option is available to excel or csv

Answer 1

Hi Shambhu,

You can use SSISDB for reference,

for example:

SELECT
    P.name AS PackageName,
    P.description AS PackageDescription,
    C.name AS ConnectionManagerName,
    C.description AS ConnectionManagerDescription,
    C.connectionstring AS ConnectionString
FROM
    [SSISDB].[catalog].[packages] AS PKG
JOIN
    [SSISDB].[catalog].[projects] AS PRJ ON PKG.project_id = PRJ.project_id
JOIN
    [SSISDB].[catalog].[package_parameters] AS PP ON PKG.package_id = PP.package_id
JOIN
    [SSISDB].[catalog].[environments] AS ENV ON PRJ.environment_id = ENV.environment_id
JOIN
    [SSISDB].[catalog].[environment_references] AS ER ON ENV.environment_id = ER.environment_id
JOIN
    [SSISDB].[catalog].[referenceables] AS REFS ON ER.reference_id = REFS.reference_id
JOIN
    [SSISDB].[catalog].[connections] AS C ON REFS.object_id = C.object_id
ORDER BY
    PackageName, ConnectionManagerName

Answer 2

Hi @Shambhu Rai,

Check below code to see if it is helpful.

;WITH XMLNAMESPACES

('www.microsoft.com/SqlServer/Dts' as p1,'www.microsoft.com/SqlServer/Dts' as DTS),

Base AS

(

 -- run just this part of the CTE to extract the SSIS XML

 SELECT

 [name],

 SSISXML = CAST(CAST([packagedata] AS varbinary(max)) AS xml)

 FROM [msdb].[dbo].[sysssispackages]

)

SELECT

 b.name,

 DTSID = n.value('(@DTS:DTSID)[1]','varchar(1000)'),

 ConnDesc = n.value('(@DTS:Description)[1]','varchar(1000)'), --DTS:refId

 refId = n.value('(@DTS:refId)[1]','varchar(1000)'), --DTS:refId

 CreationName = n.value('(@DTS:CreationName)[1]','varchar(100)'),

 ObjectName = n.value('(@DTS:ObjectName)[1]','varchar(100)'),

 PropertyExpression = n.value('(DTS:PropertyExpression/text())[1]','varchar(1000)'),

 PropertyExpression =

 pe.value('(@DTS:Name)[1]','varchar(1000)')+': ' +

 pe.value('(text())[1]','varchar(1000)'),

 ConnectionString =

 ISNULL('Retain: '+n.value('@DTS:Retain','varchar(20)'),'') +

 n.value

 ('(DTS:ObjectData/DTS:ConnectionManager/@DTS:ConnectionString)[1]','varchar(2000)')

FROM Base b

CROSS APPLY

 SSISXML.nodes('DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') ssis(n)

CROSS APPLY

 n.nodes('DTS:PropertyExpression') p(pe);

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.