Copy Dataverse data into Azure SQL

Use the Azure Synapse Link to connect your Microsoft Dataverse data to Azure Synapse Analytics to explore your data and accelerate time to insight. This article shows you how to run Azure Synapse pipelines or Azure Data Factory to copy data from Azure Data Lake Storage Gen2 to an Azure SQL Database with incremental updates feature enabled in Azure Synapse Link.

Note

Azure Synapse Link for Microsoft Dataverse was formerly known as Export to data lake. The service was renamed effective May 2021 and will continue to export data to Azure Data Lake as well as Azure Synapse Analytics.

Prerequisites

  1. Azure Synapse Link for Dataverse. This guide assumes that you've already met the prerequisites to create an Azure Synapse Link with a Synapse workspace. More information: Prerequisites for an Azure Synapse Link for Dataverse with your Azure Synapse Workspace
  2. Create an Azure Synapse Workspace or Azure Data Factory under the same Azure Active Directory (Azure AD) tenant as your Power Apps tenant.
  3. Create an Azure Synapse Link for Dataverse with the incremental folder update enabled. More information: Query and analyze the incremental updates
  4. Microsoft.EventGrid provider needs to be registered for trigger. More information: Azure portal. Note: If you are using this feature in Azure Synapse Analytics, ensure that your subscription is also registered with Data Factory resource provider, otherwise you'll get an error stating that the creation of an "Event Subscription" failed.
  5. Create an Azure SQL database with the Allow Azure services and resources to access this server property enabled. More information: What should I know when setting up my Azure SQL Database (PaaS)?
  6. Create and configure an Azure integration runtime. More information: Create Azure integration runtime - Azure Data Factory & Azure Synapse

Use the solution template

  1. In Power Apps, go to Data > Azure Synapse Link, select your desired Azure Synapse Link from the list, and then select Go to Azure Synapse workspace. Go to Azure Synapse Workspace

  2. Select Integrate > Browse gallery.

  3. Select Copy Dataverse data into Azure SQL using Synapse Link from the integration gallery.

Configure the solution template

  1. Create a linked service to Azure Data Lake Storage Gen2, which is connected to Dataverse using the appropriate authentication type. To do this, select Test connection to validate the connectivity, and then select Create.

  2. Similar to the previous steps, create a linked service to Azure SQL Database where Dataverse data will be synced.

  3. Once Inputs are configured, select Use this template. Use this template

  4. Now a trigger can be added to automate this pipeline, so that the pipeline can always process files when incremental updates are completed periodically. Go to Manage > Trigger, and create a trigger using the following properties:

    • Name: Enter a name for the trigger, such as triggerModelJson.
    • Type: Storage events.
    • Azure subscription: Select the subscription that has Azure Data Lake Storage Gen2.
    • Storage account name: Select the storage that has Dataverse data.
    • Container name: Select the container created by Azure Synapse Link.
    • Blob path ends with: /model.json
    • Event: Blob created.
    • Ignore empty blobs: Yes.
    • Start trigger: Enable Start trigger on creation.

    Create a trigger

  5. Select Continue to proceed to the next screen.

  6. On the next screen, the trigger validates the matching files. Select OK to create the trigger.

  7. Associate the trigger with a pipeline. Go to the pipeline imported earlier, and then select Add trigger > New/Edit. Create a trigger for the pipeline.

  8. Select the trigger in the earlier step, and then select Continue to proceed to the next screen where the trigger validates the matching files.

  9. Select Continue to proceed to the next screen.

  10. In the Trigger Run Parameter section, enter the below parameters, and then select OK.

    • Container: @split(triggerBody().folderPath,'/')[0]
    • Folder: @split(triggerBody().folderPath,'/')[1]
  11. After associating the trigger with the pipeline, select Validate all.

  12. Once validation succeeds, select Publish All. Select Publish all

  13. Select Publish to publish all the changes.

Add an event subscription filter

To ensure that the trigger fires only when model.json creation is complete, advanced filters need to be updated for the trigger’s event subscription. An event is registered against the storage account the first time the trigger runs.

  1. Once a trigger run completes, go to storage account > Events > Event Subscriptions.

  2. Select the event that was registered for the model.json trigger. Event subscription

  3. Select the Filters tab, and then select Add new filter. Add new filter

  4. Create the filter:

    • Key: subject
    • Operator: String does not end with
    • Value: /blobs/model.json
  5. Remove the CopyBlob parameter from data.api Value array.

  6. Select Save to deploy the additional filter. Save added filter

See also

Blog: Announcing Azure Synapse Link for Dataverse