Copy data from and to Dynamics 365 (Common Data Service) or Dynamics CRM by using Azure Data Factory

APPLIES TO: yesAzure Data Factory yesAzure Synapse Analytics (Preview)

This article outlines how to use a copy activity in Azure Data Factory to copy data from and to Microsoft Dynamics 365 and Microsoft Dynamics CRM. It builds on the copy activity overview article that presents a general overview of a copy activity.

Supported capabilities

This connector is supported for the following activities:

You can copy data from Dynamics 365 (Common Data Service) or Dynamics CRM to any supported sink data store. You also can copy data from any supported source data store to Dynamics 365 (Common Data Service) or Dynamics CRM. 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.

Dynamics versions Authentication types Linked service samples
Common Data Service

Dynamics 365 online

Dynamics CRM online
Azure Active Directory (Azure AD) service principal

Office 365
Dynamics online and Azure AD service-principal or Office 365 authentication
Dynamics 365 on-premises with internet-facing deployment (IFD)

Dynamics CRM 2016 on-premises with IFD

Dynamics CRM 2015 on-premises with IFD
IFD Dynamics on-premises with IFD and IFD authentication

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.

Tip

To copy data from Dynamics 365 Finance and Operations, you can use the Dynamics AX connector.

Prerequisites

To use this connector with Azure AD service-principal authentication, you must set up server-to-server (S2S) authentication in Common Data Service or Dynamics. Refer to this article for detailed steps.

Get started

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

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

Linked service properties

The following properties are supported for the Dynamics linked service.

Dynamics 365 and Dynamics CRM online

Property Description Required
type The type property must be set to "Dynamics", "DynamicsCrm", or "CommonDataServiceForApps". Yes
deploymentType The deployment type of the Dynamics instance. The value must be "Online" for Dynamics online. Yes
serviceUri The service URL of your Dynamics instance. An example is https://adfdynamics.crm.dynamics.com. Yes
authenticationType The authentication type to connect to a Dynamics server. Valid values are "AADServicePrincipal" and "Office365". Yes
servicePrincipalId The client ID of the Azure AD application. Yes when authentication is "AADServicePrincipal"
servicePrincipalCredentialType The credential type to use for service-principal authentication. Valid values are "ServicePrincipalKey" and "ServicePrincipalCert". Yes when authentication is "AADServicePrincipal"
servicePrincipalCredential The service-principal credential.

When you use "ServicePrincipalKey" as the credential type, servicePrincipalCredential can be a string that Azure Data Factory encrypts upon linked service deployment. Or it can be a reference to a secret in Azure Key Vault.

When you use "ServicePrincipalCert" as the credential, servicePrincipalCredential must be a reference to a certificate in Azure Key Vault.
Yes when authentication is "AADServicePrincipal"
username The username to connect to Dynamics. Yes when authentication is "Office365"
password The password for the user account you specified as the username. Mark this field with "SecureString" to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. Yes when authentication is "Office365"
connectVia The integration runtime to be used to connect to the data store. If no value is specified, the property uses the default Azure integration runtime. No for source, and yes for sink if the source linked service doesn't have an integration runtime

Note

The Dynamics connector formerly used the optional organizationName property to identify your Dynamics CRM or Dynamics 365 online instance. While that property still works, we suggest you specify the new serviceUri property instead to gain better performance for instance discovery.

Example: Dynamics online using Azure AD service-principal and key authentication

{  
    "name": "DynamicsLinkedService",  
    "properties": {  
        "type": "Dynamics",  
        "typeProperties": {  
            "deploymentType": "Online",  
            "serviceUri": "https://adfdynamics.crm.dynamics.com",  
            "authenticationType": "AADServicePrincipal",  
            "servicePrincipalId": "<service principal id>",  
            "servicePrincipalCredentialType": "ServicePrincipalKey",  
            "servicePrincipalCredential": "<service principal key>"
        },  
        "connectVia": {  
            "referenceName": "<name of Integration Runtime>",  
            "type": "IntegrationRuntimeReference"  
        }  
    }  
}  

Example: Dynamics online using Azure AD service-principal and certificate authentication

{ 
    "name": "DynamicsLinkedService", 
    "properties": { 
        "type": "Dynamics", 
        "typeProperties": { 
            "deploymentType": "Online", 
            "serviceUri": "https://adfdynamics.crm.dynamics.com", 
            "authenticationType": "AADServicePrincipal", 
            "servicePrincipalId": "<service principal id>", 
            "servicePrincipalCredentialType": "ServicePrincipalCert", 
            "servicePrincipalCredential": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<AKV reference>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<certificate name in AKV>" 
            } 
        }, 
        "connectVia": { 
            "referenceName": "<name of Integration Runtime>", 
            "type": "IntegrationRuntimeReference" 
        } 
    } 
} 

Example: Dynamics online using Office 365 authentication

{
    "name": "DynamicsLinkedService",
    "properties": {
        "type": "Dynamics",
        "typeProperties": {
            "deploymentType": "Online",
            "serviceUri": "https://adfdynamics.crm.dynamics.com",
            "authenticationType": "Office365",
            "username": "test@contoso.onmicrosoft.com",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dynamics 365 and Dynamics CRM on-premises with IFD

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

Property Description Required
type The type property must be set to "Dynamics", "DynamicsCrm", or "CommonDataServiceForApps". Yes.
deploymentType The deployment type of the Dynamics instance. The value must be "OnPremisesWithIfd" for Dynamics on-premises with IFD. Yes.
hostName The host name of the on-premises Dynamics server. Yes.
port The port of the on-premises Dynamics server. No. The default value is 443.
organizationName The organization name of the Dynamics instance. Yes.
authenticationType The authentication type to connect to the Dynamics server. Specify "Ifd" for Dynamics on-premises with IFD. Yes.
username The username to connect to Dynamics. Yes.
password The password for the user account you specified for the username. You can mark this field with "SecureString" to store it securely in Data Factory. Or you can store a password in Key Vault and let the copy activity pull from there when it does data copy. Learn more from Store credentials in Key Vault. Yes.
connectVia The integration runtime to be used to connect to the data store. If no value is specified, the property uses the default Azure integration runtime. No for source and yes for sink.

Example: Dynamics on-premises with IFD using IFD 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": "Ifd",
            "username": "test@contoso.onmicrosoft.com",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "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:

Property Description Required
type The type property of the dataset must be set to "DynamicsEntity", "DynamicsCrmEntity", or "CommonDataServiceForAppsEntity". Yes
entityName The logical name of the entity to retrieve. No for source if the activity source is specified as "query" and yes for sink

Example

{
    "name": "DynamicsDataset",
    "properties": {
        "type": "DynamicsEntity",
        "schema": [],
        "typeProperties": {
            "entityName": "account"
        },
        "linkedServiceName": {
            "referenceName": "<Dynamics linked service name>",
            "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:

Property Description Required
type The type property of the copy activity source must be set to "DynamicsSource", "DynamicsCrmSource", or "CommonDataServiceForAppsSource". Yes
query FetchXML is a proprietary query language that is used in Dynamics online and on-premises. See the following example. To learn more, see Build queries with FetchXML. No if entityName in the dataset is specified

Note

The PK column will always be copied out even if the column projection you configure in the FetchXML query doesn't contain it.

Important

  • When you copy data from Dynamics, explicit column mapping from Dynamics to sink is optional. But we highly recommend the mapping to ensure a deterministic copy result.
  • When Data Factory imports a schema in the authoring UI, it infers the schema. It does so by sampling the top rows from the Dynamics query result to initialize the source column list. In that case, columns with no values in the top rows are omitted. The same behavior applies to copy executions if there is no explicit mapping. You can review and add more columns into the mapping, which are honored during copy runtime.

Example

"activities":[
    {
        "name": "CopyFromDynamics",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Dynamics input dataset>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "DynamicsSource",
                "query": "<FetchXML Query>"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Sample FetchXML query

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="marketingonly" />
    <attribute name="modifiedon" />
    <order attribute="modifiedon" descending="false" />
    <filter type="and">
      <condition attribute ="modifiedon" operator="between">
        <value>2017-03-10 18:40:00z</value>
        <value>2017-03-12 20:40:00z</value>
      </condition>
    </filter>
  </entity>
</fetch>

Dynamics as a sink type

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

Property Description Required
type The type property of the copy activity sink must be set to "DynamicsSink", "DynamicsCrmSink", or "CommonDataServiceForAppsSink". Yes.
writeBehavior The write behavior of the operation. The value must be "Upsert". Yes
alternateKeyName The alternate key name defined on your entity to do an upsert. No.
writeBatchSize The row count of data written to Dynamics in each batch. No. The default value is 10.
ignoreNullValues Whether to ignore null values from input data other than key fields during a write operation.

Valid values are TRUE and FALSE:
  • TRUE: Leave the data in the destination object unchanged when you do an upsert or update operation. Insert a defined default value when you do an insert operation.
  • FALSE: Update the data in the destination object to a null value when you do an upsert or update operation. Insert a null value when you do an insert operation.
No. The default value is FALSE.

Note

The default value for both the sink writeBatchSize and the copy activity parallelCopies for the Dynamics sink is 10. Therefore, 100 records are concurrently submitted by default to Dynamics.

For Dynamics 365 online, there's a limit of two 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": "<input dataset>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Dynamics output dataset>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "DynamicsSink",
                "writeBehavior": "Upsert",
                "writeBatchSize": 10,
                "ignoreNullValues": true
            }
        }
    }
]

Data type mapping for Dynamics

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

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

Dynamics data type Data Factory interim data type Supported as source Supported as sink
AttributeTypeCode.BigInt Long
AttributeTypeCode.Boolean Boolean
AttributeType.Customer GUID ✓ (See guidance)
AttributeType.DateTime Datetime
AttributeType.Decimal Decimal
AttributeType.Double Double
AttributeType.EntityName String
AttributeType.Integer Int32
AttributeType.Lookup GUID ✓ (See guidance)
AttributeType.ManagedProperty Boolean
AttributeType.Memo String
AttributeType.Money Decimal
AttributeType.Owner GUID ✓ (See guidance)
AttributeType.Picklist Int32
AttributeType.Uniqueidentifier GUID
AttributeType.String String
AttributeType.State Int32
AttributeType.Status Int32

Note

The Dynamics data types AttributeType.CalendarRules, AttributeType.MultiSelectPicklist, and AttributeType.PartyList aren't supported.

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:

  1. Make your source contains both the field value and the corresponding target entity name.

    • If all records map to the same target entity, ensure one of the following conditions:
      • Your source data has a column that stores the target entity name.
      • You've added an additional column in the copy activity source to define the target entity.
    • If different records map to different target entities, make sure your source data has a column that stores the corresponding target entity name.
  2. Map both the value and entity-reference columns from source to sink. The entity-reference column must be mapped to a virtual column with the special naming pattern {lookup_field_name}@EntityReference. The column doesn't actually exist in Dynamics. It's used to indicate this column is the metadata column of the given multitarget lookup field.

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.

Dynamics lookup-field column mapping

If all of your source records map to the same target entity and your source data doesn't contain the target entity name, here is a shortcut: in the copy activity source, add an additional 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.

Dynamics lookup-field adding an entity-reference column

Lookup activity properties

To learn details about the properties, see Lookup activity.

Next steps

For a list of data stores the copy activity in Data Factory supports as sources and sinks, see Supported data stores.