Fetch Metadata from SSIS Package stored in SSISDB

Erin Kimak 46 Reputation points
2023-01-18T04:09:51.1466667+00:00

Hii Team I have one usecase where I need to fetch Metadata from SSIS Package stored in SSISDB.

Is there any way we can get Metadata from SSIS package. I had one stored in SSISDB and need to fetch its Metadata like given below:

o   Package Name
o   Sources (DB/File/etc)
o   Targets (Table/File/File Location)
o   Stored Procs used in package
o   Source Tables/Columns
o   Package Connections
o   Target file name(s)/File Type etc.

Is there any API to fetch all Technical Metadata of SSIS Package or any other way to fetch this things from SSISDB any kind of help is appreciated. Thank You

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,643 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,296 Reputation points
    2023-01-18T06:30:41.0833333+00:00

    Hi @Erin Kimak

    As far as I know, not all the metadata could be fetched with tsql.

    You may check below two articles for details.

    How to retrieve information about SSIS packages stored in MSDB Database

    SSIS - Read SSIS Package File(.dtsx) for Information [Connection Managers,Transformations,Variables,Configuration,Expressions,Log Providers]

    Regards，

    Zoe Hui

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

    0 comments