Copy Dataverse data from ADLS Gen2 to Azure SQL DB leveraging Azure Synapse Link

[Guest shireenbahadur]

We have added a new template in the ADF and Azure Synapse Pipelines template gallery that allows you to copy data from ADLS (Azure Data Lake Storage) Gen2 account to an Azure SQL Database. This template is a code sample, to be used as guidance for customers that are using the preview feature of incremental updates in Azure Synapse Link for Dataverse, which provides incremental folders to help query and analyze the incremental updates made to Dataverse data during a user-specified time internal. More detail about incremental updates can be found here.

 

 

 

 

 

 

 

 

This template uses a Lookup activity to retrieve the model.json file from Azure Data Lake Storage Gen2 and passes the file to a subsequent ForEach activity. Then, the ForEach activity has a script which creates/updates the table based on the schema defined in the model.json file and iterates each CDM entity to the dataflow. Lastly, a dataflow resides in the ForEach activity to copy each file whose location is provided under the partition's sections of the model.json data store to the destination data store.

 

 

 

 

 

 

 

 

To get started using this template, please follow the steps in the documentation link.

 

 

 

Note: This ADF template is a code sample. You are more than encouraged to use this template as a guidance to test out the functionality of retrieving data from ADLS Gen 2 storage to Azure SQL DB using the pipeline provided. However, we are always open to getting your feedback! Please provide any suggestions or questions about this template in our forums ADF Stack Overflow or Microsoft Q&A Portal.

 

Continue reading...