How to get lineage from SQL Server Integration Services (SSIS) into Microsoft Purview

This article elaborates on the data lineage aspects of SQL Server Integration Services (SSIS) in Microsoft Purview.

Prerequisites

Supported scenarios

The current scope of support includes the lineage extraction from SSIS packages executed by Azure Data Factory SSIS integration runtime.

On premises SSIS lineage extraction is not supported yet.

Only source and destination are supported for Microsoft Purview SSIS lineage running from Data Factory’s SSIS Execute Package activity. Transformations under SSIS are not yet supported.

Supported data stores

Data store Supported
Azure Blob Storage Yes
Azure Data Lake Storage Gen1 Yes
Azure Data Lake Storage Gen2 Yes
Azure Files Yes
Azure SQL Database * Yes
Azure SQL Managed Instance * Yes
Azure Synapse Analytics * Yes
SQL Server * Yes

* Microsoft Purview currently doesn't support query or stored procedure for lineage or scanning. Lineage is limited to table and view sources only.

How to bring SSIS lineage into Microsoft Purview

Step 1. Connect a Data Factory to Microsoft Purview

Step 2. Trigger SSIS activity execution in Azure Data Factory

You can run SSIS package with Execute SSIS Package activity or run SSIS package with Transact-SQL in ADF SSIS Integration Runtime.

Once Execute SSIS Package activity finishes the execution, you can check lineage report status from the activity output in Data Factory activity monitor. ssis-status

Step 3. Browse lineage Information in your Microsoft Purview account

  • You can browse the Data Catalog by choosing asset type “SQL Server Integration Services”.

Browser assets browser-assets-ssis browse-assets-ssis-package

  • You can also search the Data Catalog using keywords.

search-ssis

  • You can view lineage information for an SSIS Execute Package activity and open in Data Factory to view/edit the activity settings.

show-ssis-lineage

  • You can choose one data source to drill into how the columns in the source are mapped to the columns in the destination.

show-ssis-lineage-drill-in

Next steps