ssis packages and connection manager inputs

Shambhu Rai 876 Reputation points
2023-05-20T11:39:48.6966667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,776 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,356 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,076 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vahid Ghafarpour 1,665 Reputation points
    2023-05-21T06:45:36.2833333+00:00

    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
  2. ZoeHui-MSFT 22,061 Reputation points
    2023-05-22T06:01:03.2633333+00:00

    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.