Bring SSIS ETL lineage into Azure Purview Data Map

Published 02-25-2021 11:44 PM 1,342 Views
Microsoft

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:

%3CLINGO-SUB%20id%3D%22lingo-sub-2168081%22%20slang%3D%22en-US%22%3EBring%26nbsp%3BSSIS%26nbsp%3BETL%26nbsp%3Blineage%26nbsp%3Binto%26nbsp%3BAzure%26nbsp%3BPurview%20Data%26nbsp%3BMap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2168081%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20comes%20part%20three%20in%20the%26nbsp%3B%E2%80%9CADF%2FPurview%20integration%E2%80%9D%20blog%20series.%26nbsp%3B%20In%20this%20blog%20we%20will%20focus%20on%20bringing%20SSIS%20ETL%20linage%20into%20Azure%20Purview.%26nbsp%3B%20If%20you%20want%20to%20learn%20what%20we%20covered%20in%20part%20one%20and%20two%20in%20this%20series%2C%20please%20go%20back%20and%20check%20out%20%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-data-factory%2Fanalyze-root-cause-and-impact-using-adf-etl-lineage-in-azure%2Fba-p%2F2128396%22%20target%3D%22_blank%22%3EAnalyze%20root%20cause%20and%20impact%20using%20ADF%20ETL%20lineage%20in%20Azure%20Purview%3C%2FA%3E%20%26nbsp%3Band%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-data-factory%2Fbootstrap-etl-process-by-bringing-azure-purview-assets-into%2Fba-p%2F2149194%22%20target%3D%22_blank%22%3EBootstrap%20ETL%20process%20by%20bringing%20Azure%20Purview%20assets%20into%20Azure%20Data%20Factory%3C%2FA%3E%3CSPAN%3E.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnterprises%20are%20increasingly%20migrating%20existing%20SQL%20Server%20Integration%20Services%20(SSIS)%20projects%20and%20packages%20from%20on-premises%20to%20Azure%20for%20cost%20reduction%20as%20well%20as%20scalability%20and%20high%20availability%20enhancements.%26nbsp%3B%20With%20the%20number%20of%20packages%20often%20in%20the%20hundreds%20if%20not%20thousands%2C%20data%20engineers%20in%20charge%20of%20operating%20the%20ETL%20process%20often%20grapple%20with%20the%20challenge%20of%20ensuring%20the%20freshness%20of%20the%20produced%20data%20and%20downstream%20data%20consumers%20often%20wondering%20whether%20they%20can%20trust%20the%20quality%20of%20the%20data%20for%20their%20business-critical%20reports.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESQL%20Server%20Integration%20Services%20(SSIS)%3C%2FSTRONG%3E%20is%20now%20integrated%20with%20Azure%20Purview%20to%20address%20these%20challenges!%20%26nbsp%3BYou%20can%20bring%20data%20lineage%20from%20%3CSTRONG%3EAzure%20Data%20Factory%20SSIS%20Integration%20Runtime%20%3C%2FSTRONG%3Eto%20Azure%20Purview%20for%20root%20cause%20analysis%20and%20impact%20analysis.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20do%20not%20have%20ADF%20SSIS%20Integration%20Runtime%2C%20please%20check%20out%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Fintegration-services%2Flift-shift%2Fssis-azure-lift-shift-ssis-packages-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELift%20and%20shift%20SQL%20Server%20Integration%20Services%20workloads%20to%20the%20cloud%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20steps%20below%20describe%20how%20to%20bring%20SSIS%20lineage%20into%20Azure%20Purview.%3C%2FP%3E%0A%3CP%3EStep%201%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpurview%2Fcreate-catalog-portal%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECreate%20an%20Azure%20Purview%20account%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%202%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-factory%2Fconnect-data-factory-to-azure-purview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EConnect%20a%20Data%20Factory%20to%20Azure%20Purview%20%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_9-1614324921886.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258284i44571000333D2DC8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_9-1614324921886.png%22%20alt%3D%22Chunhua_9-1614324921886.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%203%3A%20Trigger%20SSIS%20activity%20execution%20in%20Azure%20Data%20Factory%3C%2FP%3E%0A%3CP%3EYou%20can%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-factory%2Fhow-to-invoke-ssis-package-ssis-activity%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Erun%20SSIS%20package%20with%20Execute%20SSIS%20Package%20activity%3C%2FA%3E%20or%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Fintegration-services%2Flift-shift%2Fssis-azure-run-packages%23sproc_activity%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Erun%20SSIS%20package%20with%20Transact-SQL%3C%2FA%3E%20in%20ADF%20SSIS%20Integration%20Runtime.%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20Execute%20SSIS%20Package%20activity%20finishes%20the%20execution%2C%20you%20can%20check%20lineage%20report%20status%20from%20the%20activity%20output%20in%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-factory%2Fmonitor-visually%23monitor-activity-runs%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EData%20Factory%20activity%20monitor%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_10-1614324921891.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258286i39C23009C826BD1D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_10-1614324921891.png%22%20alt%3D%22Chunhua_10-1614324921891.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%204%3A%20Now%20you%20are%20ready%20to%20browse%20lineage%20Information%20in%20your%20Azure%20Purview%20account.%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EYou%20can%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpurview%2Fhow-to-browse-catalog%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ebrowse%20the%20Data%20Catalog%3C%2FA%3E%20by%20choosing%20asset%20type%20%E2%80%9CSQL%20Server%20Integration%20Services%E2%80%9D.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_11-1614324921899.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258285i6B01B4CB9FB998A1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_11-1614324921899.png%22%20alt%3D%22Chunhua_11-1614324921899.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_12-1614324921901.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258287iF9AE92B5C6D7C4E1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_12-1614324921901.png%22%20alt%3D%22Chunhua_12-1614324921901.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_13-1614324921902.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258288iD268EBC9044241BA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_13-1614324921902.png%22%20alt%3D%22Chunhua_13-1614324921902.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_14-1614324921904.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258289iCB30D6E64F3B8F2A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_14-1614324921904.png%22%20alt%3D%22Chunhua_14-1614324921904.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%222%22%3E%0A%3CLI%3EAlternatively%20you%20can%20also%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpurview%2Fhow-to-search-catalog%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Esearch%20the%20Data%20Catalog%3C%2FA%3E%20using%20keywords%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_15-1614324921912.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258291iD28709AB62D9C8AB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_15-1614324921912.png%22%20alt%3D%22Chunhua_15-1614324921912.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%223%22%3E%0A%3CLI%3EYou%20can%20view%20lineage%20information%20for%20an%20SSIS%20Execute%20Package%20activity%20and%20have%20the%20option%20to%20open%20in%20Data%20Factory%20to%20view%2Fedit%20the%20activity%20settings.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_16-1614324921916.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258290i3299FF476EFF20AD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_16-1614324921916.png%22%20alt%3D%22Chunhua_16-1614324921916.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%224%22%3E%0A%3CLI%3EYou%20can%20choose%20one%20data%20source%20to%20drill%20into%20how%20the%20columns%20in%20the%20source%20are%20mapped%20to%20the%20columns%20in%20the%20destination.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chunhua_17-1614324921926.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258292iC00F3978248D4993%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Chunhua_17-1614324921926.png%22%20alt%3D%22Chunhua_17-1614324921926.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EMore%26nbsp%3BResources%3C%2FSTRONG%3E%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpurview%2Fcreate-catalog-portal%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECreate%20an%20Azure%20Purview%20account%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-factory%2Fconnect-data-factory-to-azure-purview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EConnect%20a%20Data%20Factory%20to%20Azure%20Purview%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fpurview%2Fcatalog-lineage-user-guide%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EData%20Catalog%20lineage%20user%20guide%3C%2FA%3E%2C%20provides%20an%20overview%20of%20the%20data%20lineage%20features%20in%20Azure%20Purview%20Data%20Catalog.%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpurview%2Fhow-to-link-azure-data-factory%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Data%20Factory%20Lineage%3C%2FA%3E%2C%20gets%20into%20the%20details%20of%20the%20coverage%20scope%20and%20supported%20lineage%20patterns.%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2168081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EThis%20blog%20describes%20how%20to%20bring%20SSIS%20ETL%20lineage%20into%20Azure%20Purview%20Data%20Map.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2168081%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20ETL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Feb 25 2021 11:44 PM
Updated by: