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  


Chunhua_9-1614324921886.png


 


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.


Chunhua_10-1614324921891.png


 


 


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”.


Chunhua_11-1614324921899.png


 


Chunhua_12-1614324921901.png


 


Chunhua_13-1614324921902.png


 


Chunhua_14-1614324921904.png


 



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


Chunhua_15-1614324921912.png


 


 



  1. 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.


Chunhua_16-1614324921916.png


 


 



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


Chunhua_17-1614324921926.png


 


 


More Resources:


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