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.
Step 3: Browse lineage Information in your Microsoft Purview account
- You can browse the Data Catalog by choosing asset type “SQL Server Integration Services”.
- You can also search the Data Catalog using keywords.
- You can view lineage information for an SSIS Execute Package activity and open in Data Factory to view/edit the activity settings.
- You can choose one data source to drill into how the columns in the source are mapped to the columns in the destination.
