Copy and transform data in Dynamics 365 (Microsoft Dataverse) or Dynamics CRM using Azure Data Factory or Azure Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article outlines how to use a copy activity in Azure Data Factory or Synapse pipelines to copy data from and to Dynamics 365 (Microsoft Dataverse) or Dynamics CRM, and use a data flow to transform data in Dynamics 365 (Microsoft Dataverse) or Dynamics CRM. To learn more, read the Azure Data Factory and the Azure Synapse Analytics introduction articles.

Supported capabilities

This connector is supported for the following activities:

① Azure integration runtime ② Self-hosted integration runtime

For a list of data stores that a copy activity supports as sources and sinks, see the Supported data stores table.

This Dynamics connector supports Dynamics versions 7 through 9 for both online and on-premises. More specifically:

• Version 7 maps to Dynamics CRM 2015.
• Version 8 maps to Dynamics CRM 2016 and the early version of Dynamics 365.
• Version 9 maps to the later version of Dynamics 365.

Refer to the following table of supported authentication types and configurations for Dynamics versions and products.

For Dynamics 365 specifically, the following application types are supported:

• Dynamics 365 for Sales
• Dynamics 365 for Customer Service
• Dynamics 365 for Field Service
• Dynamics 365 for Project Service Automation
• Dynamics 365 for Marketing

This connector doesn't support other application types like Finance, Operations, and Talent.

This Dynamics connector is built on top of Dynamics XRM tooling.

Prerequisites

To use this connector with Microsoft Entra service principal authentication, you must set up server-to-server (S2S) authentication in Dataverse or Dynamics. First register the application user (Service Principal) in Microsoft Entra ID. You can find out how to do this here. During application registration you'll need to create that user in Dataverse or Dynamics and grant permissions. Those permissions can either be granted directly or indirectly by adding the application user to a team which has been granted permissions in Dataverse or Dynamics. You can find more information on how to set up an application user to authenticate with Dataverse here.

Get started

To perform the copy activity with a pipeline, you can use one of the following tools or SDKs:

• Copy Data tool
• Azure portal
• .NET SDK
• Python SDK
• Azure PowerShell
• REST API
• Azure Resource Manager template

Create a linked service to Dynamics 365 (Microsoft Dataverse) or Dynamics CRM using UI

Use the following steps to create a linked service to Dynamics 365 in the Azure portal UI.

1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then select New:

   • Azure Data Factory
   • Azure Synapse

   

2. Search for Dynamics or Dataverse and select the Dynamics 365 (Microsoft Dataverse) or Dynamics CRM connector.

   

   

3. Configure the service details, test the connection, and create the new linked service.

   

Connector configuration details

The following sections provide details about properties that are used to define entities specific to Dynamics.

Linked service properties

The following properties are supported for the Dynamics linked service.

Dynamics 365 and Dynamics CRM online

Example: Dynamics online using Microsoft Entra service principal and key authentication

{  
    "name": "DynamicsLinkedService",  
    "properties": {  
        "type": "Dynamics",  
        "typeProperties": {  
            "deploymentType": "Online",  
            "serviceUri": "https://.crm[x].dynamics.com",  
            "authenticationType": "AADServicePrincipal",  
            "servicePrincipalId": "",  
            "servicePrincipalCredentialType": "ServicePrincipalKey",  
            "servicePrincipalCredential": ""
        },  
        "connectVia": {  
            "referenceName": "",  
            "type": "IntegrationRuntimeReference"  
        }  
    }  
}  

Example: Dynamics online using Microsoft Entra service principal and certificate authentication

{ 
    "name": "DynamicsLinkedService", 
    "properties": { 
        "type": "Dynamics", 
        "typeProperties": { 
            "deploymentType": "Online", 
            "serviceUri": "https://.crm[x].dynamics.com", 
            "authenticationType": "AADServicePrincipal", 
            "servicePrincipalId": "", 
            "servicePrincipalCredentialType": "ServicePrincipalCert", 
            "servicePrincipalCredential": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "" 
            } 
        }, 
        "connectVia": { 
            "referenceName": "", 
            "type": "IntegrationRuntimeReference" 
        } 
    } 
} 

Example: Dynamics online using Office 365 authentication

{
    "name": "DynamicsLinkedService",
    "properties": {
        "type": "Dynamics",
        "typeProperties": {
            "deploymentType": "Online",
            "serviceUri": "https://.crm[x].dynamics.com",
            "authenticationType": "Office365",
            "username": "test@contoso.onmicrosoft.com",
            "password": {
                "type": "SecureString",
                "value": ""
            }
        },
        "connectVia": {
            "referenceName": "",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: Dynamics online using user-assigned managed identity authentication

{
    "name": "DynamicsLinkedService",
    "properties": {
        "type": "Dynamics",
        "typeProperties": {
            "deploymentType": "Online",
            "serviceUri": "https://.crm[x].dynamics.com",
            "authenticationType": "ManagedIdentity",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dynamics 365 and Dynamics CRM on-premises with IFD

More properties that compare to Dynamics online are hostName and port.

Example: Dynamics on-premises with IFD using Active Directory authentication

{
    "name": "DynamicsLinkedService",
    "properties": {
        "type": "Dynamics",
        "description": "Dynamics on-premises with IFD linked service using IFD authentication",
        "typeProperties": {
            "deploymentType": "OnPremisesWithIFD",
            "hostName": "contosodynamicsserver.contoso.com",
            "port": 443,
            "organizationName": "admsDynamicsTest",
            "authenticationType": "ActiveDirectoryAuthentication",
            "domain": "< Active Directory domain >", 
            "username": "test@contoso.onmicrosoft.com",
            "password": {
                "type": "SecureString",
                "value": ""
            }
        },
        "connectVia": {
            "referenceName": "",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dataset properties

For a full list of sections and properties available for defining datasets, see the Datasets article. This section provides a list of properties supported by Dynamics dataset.

To copy data from and to Dynamics, the following properties are supported:

Example

{
    "name": "DynamicsDataset",
    "properties": {
        "type": "DynamicsEntity",
        "schema": [],
        "typeProperties": {
            "entityName": "account"
        },
        "linkedServiceName": {
            "referenceName": "",
            "type": "linkedservicereference"
        }
    }
}

Copy activity properties

For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by Dynamics source and sink types.

Dynamics as a source type

To copy data from Dynamics, the copy activity source section supports the following properties:

Example

"activities":[
    {
        "name": "CopyFromDynamics",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "
",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "DynamicsSource",
                "query": ""
            },
            "sink": {
                "type": ""
            }
        }
    }
]

Sample FetchXML query

  
    
    
    
    
    
    
      
        2017-03-10 18:40:00z
        2017-03-12 20:40:00z
      
    
  

Dynamics as a sink type

To copy data to Dynamics, the copy activity sink section supports the following properties:

For Dynamics 365 online, there's a limit of 52 concurrent batch calls per organization. If that limit is exceeded, a "Server Busy" exception is thrown before the first request is ever run. Keep writeBatchSize at 10 or less to avoid such throttling of concurrent calls.

The optimal combination of writeBatchSize and parallelCopies depends on the schema of your entity. Schema elements include the number of columns, row size, and number of plug-ins, workflows, or workflow activities hooked up to those calls. The default setting of writeBatchSize (10) × parallelCopies (10) is the recommendation according to the Dynamics service. This value works for most Dynamics entities, although it might not give the best performance. You can tune the performance by adjusting the combination in your copy activity settings.

Example

"activities":[
    {
        "name": "CopyToDynamics",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": ""
            },
            "sink": {
                "type": "DynamicsSink",
                "writeBehavior": "Upsert",
                "writeBatchSize": 10,
                "ignoreNullValues": true
            }
        }
    }
]

When you select an elastic tables in the copy activity sink, the connector mapping supports the partitionid column. You can map your source data column to the sink's partitionid column. If not mapped, the primary key value is used as the default value for the partitionid column.

partitionid can be used in Dataverse alternate keys or primary key scenarios on the write path. Each elastic table contains a system-defined partitionid column and has an alternate key named KeyForNoSqlEntityWithPKPartitionId, which combines the primary key of the table with the partitionid column. For more information, see this article.

Retrieving data from views

To retrieve data from Dynamics views, you need to get the saved query of the view, and use the query to get the data.

There are two entities that store different types of view: "saved query" stores system view and "user query" stores user view. To get the information of the views, refer to the following FetchXML query and replace the "TARGETENTITY" with savedquery or userquery. Each entity type has more available attributes that you can add to the query based on your need. Learn more about savedquery entity and userquery entity.

You can also add filters to filter the views. For example, add the following filter to get a view named "My Active Accounts" in account entity.

Data type mapping for Dynamics

When you copy data from Dynamics, the following table shows mappings from Dynamics data types to interim data types within the service. To learn how a copy activity maps to a source schema and a data type map to a sink, see Schema and data type mappings.

Configure the corresponding interim data type in a dataset structure that is based on your source Dynamics data type by using the following mapping table:

Writing data to a lookup field

To write data into a lookup field with multiple targets like Customer and Owner, follow this guidance and example:

Setting the Owner field

When setting the Owner field in Dynamics 365 (Microsoft Dataverse) or Dynamics CRM, it's important to provide a valid reference. The valid options for the @EntityReference are:

• systemuser: This refers to an individual user within the system.
• team: This refers to a team of users within the organization.

Ensure that the value provided corresponds to one of these options to avoid errors during data transformation.

For example, assume the source has these two columns:

• CustomerField column of type GUID, which is the primary key value of the target entity in Dynamics.
• Target column of type String, which is the logical name of the target entity.

Also assume you want to copy such data to the sink Dynamics entity field CustomerField of type Customer.

In copy-activity column mapping, map the two columns as follows:

• CustomerField to CustomerField. This mapping is the normal field mapping.
• Target to CustomerField@EntityReference. The sink column is a virtual column representing the entity reference. Input such field names in a mapping, as they won't show up by importing schemas.

If all of your source records map to the same target entity and your source data doesn't contain the target entity schema name, here's a shortcut: in the copy activity source, add another column. Name the new column by using the pattern {lookup_field_name}@EntityReference, set the value to the target entity name, then proceed with column mapping as usual. If your source and sink column names are identical, you can also skip explicit column mapping because copy activity by default maps columns by name.

Writing data to a lookup field via alternative keys

To write data into a lookup field using alternate key columns, follow this guidance and example:

Mapping data flow properties

When transforming data in mapping data flow, you can read from and write to tables in Dynamics. For more information, see the source transformation and sink transformation in mapping data flows. You can choose to use a Dynamics dataset or an inline dataset as source and sink type.

Source transformation

The below table lists the properties supported by Dynamics. You can edit these properties in the Source options tab.

Dynamics source script example

When you use Dynamics dataset as source type, the associated data flow script is:

source(allowSchemaDrift: true,
	validateSchema: false,
	query: '') ~> DynamicsSource

If you use inline dataset, the associated data flow script is:

source(allowSchemaDrift: true,
	validateSchema: false,
	store: 'dynamics',
	format: 'dynamicsformat',
	entity: 'Entity1',
	query: '') ~> DynamicsSource

Sink transformation

The below table lists the properties supported by Dynamics sink. You can edit these properties in the Sink options tab.

Dynamics sink script example

When you use Dynamics dataset as sink type, the associated data flow script is:

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    deletable:true,
    insertable:true,
    updateable:true,
    upsertable:true,
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> DynamicsSink

If you use inline dataset, the associated data flow script is:

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    store: 'dynamics',
    format: 'dynamicsformat',
    entity: 'Entity1',
    deletable: true,
    insertable: true,
    updateable: true,
    upsertable: true,
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> DynamicsSink

Lookup activity properties

To learn details about the properties, see Lookup activity.

Related content

For a list of supported data stores the copy activity as sources and sinks, see Supported data stores.