Bring SSIS ETL lineage into Azure Purview Data Map

This article is contributed. See the original author and article here.

Here comes part three in the “ADF/Purview integration” blog series.  In this blog we will focus on bringing SSIS ETL linage into Azure Purview.  If you want to learn what we covered in part one and two in this series, please go back and check out  Analyze root cause and impact using ADF ETL lineage in Azure Purview  and Bootstrap ETL process by bringing Azure Purview assets into Azure Data Factory.

 

Enterprises are increasingly migrating existing SQL Server Integration Services (SSIS) projects and packages from on-premises to Azure for cost reduction as well as scalability and high availability enhancements.  With the number of packages often in the hundreds if not thousands, data engineers in charge of operating the ETL process often grapple with the challenge of ensuring the freshness of the produced data and downstream data consumers often wondering whether they can trust the quality of the data for their business-critical reports.

 

SQL Server Integration Services (SSIS) is now integrated with Azure Purview to address these challenges!  You can bring data lineage from Azure Data Factory SSIS Integration Runtime to Azure Purview for root cause analysis and impact analysis.

 

If you do not have ADF SSIS Integration Runtime, please check out Lift and shift SQL Server Integration Services workloads to the cloud.

 

The steps below describe how to bring SSIS lineage into Azure Purview.

Step 1: Create an Azure Purview account

 

Step 2: Connect a Data Factory to Azure Purview  

 

Step 3: 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 4: Now you are ready to browse lineage Information in your Azure Purview account.

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

 

 

 

 

2. Alternatively you can also search the Data Catalog using keywords

 

 

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

 

 

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

 

 

More Resources:

• Create an Azure Purview account


• Connect a Data Factory to Azure Purview


• Data Catalog lineage user guide, provides an overview of the data lineage features in Azure Purview Data Catalog.


• Azure Data Factory Lineage, gets into the details of the coverage scope and supported lineage patterns.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.