Fetch Metadata from SSIS Package stored in SSISDB

Erin Kimak 46 Reputation points

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,490 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 32,506 Reputation points

    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]


    Zoe Hui

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

    0 comments No comments