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

APPLIES TO: Azure Data FactoryAzure Synapse Analytics

This article describes how to use a copy activity in Azure Data Factory or Synapse pipelines to copy data to and from Dynamics 365 (Microsoft Dataverse) or Dynamics CRM and use a dataflow to import data into Dynamics 365 (Microsoft Dataverse) or to transform Dynamics CRM . For more information, read theAzure Data Factoryit's himAzure Synapse Analyticsintroductory article.

Supported features

This connector supports the following activities:

① Integration Runtime do Azure ② Integration Runtime autohospedado

For a list of data stores that support copy activity as sources and sinks, seeCompatible data storesTisch.

This Dynamics connector is compatible with Dynamics versions 7 through 9, both online and locally. More accurate:

• Version 7 is associated with Dynamics CRM 2015.
• Version 8 is associated with Dynamics CRM 2016 and previous version of Dynamics 365.
• Version 9 maps to the latest version of Dynamics 365.

See the following table of supported authentication types and settings for Dynamics versions and products.

Specifically for Dynamics 365, the following types of apps are supported:

• Dynamics 365 for sales
• Dynamics 365 for customer service
• Dynamics 365 for field service
• Dynamics 365 for Project Services Automation
• Dynamics 365 para marketing

This connector does not support other application types such as Finance, Operations, and Talent.

This Dynamics connector createsDynamics XRM Tools.

requirements

To use this connector with Azure AD service principal authentication, you must configure server-to-server (S2S) authentication in Dataverse or Dynamics. First, register the application user (service principal) in Azure Active Directory. Find out how hereon here. During application registration, you must create this user in Dataverse or Dynamics and grant permissions. These permissions can be granted directly or indirectly by adding the app user to a team that has been granted permissions in Dataverse or Dynamics. Learn more about configuring an app user to authenticate with Dataverse hereon here.

starting

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

• The Copy Data tool
• the blue portal
• O SDK .NET
• O SDK de Python
• Azure-PowerShell
• Die REST-API
• The Azure Resource Manager template

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

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

1. In the Azure Data Factory or Synapse workspace, go to the Manage tab, select Linked Services and click New:

   • Azure Data Factory
   • Azure-Synapse

   

2. Procure Dynamics ou Dataverse e selecione Connector for Dynamics 365 (Microsoft Dataverse) ou Dynamics CRM.

   

   

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

   

Connector configuration details

The following sections provide details on the properties used to define specific Dynamics entities.

Linked service properties

The following properties are supported by Dynamics Linked Service.

Dynamics 365 e Dynamics CRM online

Example: Dynamics Online with Azure AD service principal and key authentication

{ "name": "DynamicsLinkedService", "properties": { "type": "Dynamics", "typeProperties": { "deploymentType": "Online", "serviceUri": "https://<nombre de la organización> . crm[ x].dynamics.com", "authenticationType": "AADServicePrincipal", "servicePrincipalId": "<Dienstprinzipal-ID>", "servicePrincipalCredentialType": "ServicePrincipalKey", "servicePrincipalCredential": "<Dienstprinzipalschlüssel>" } , "connectVia" : { "referenceName": "<Nome de Integration Runtime>", "type": "IntegrationRuntimeReference" } } }

Example: Dynamics Online with Azure AD service principal and certificate authentication

{ "name": "DynamicsLinkedService", "properties": { "type": "Dynamics", "typeProperties": { "deploymentType": "Online", "serviceUri": "https://<nombre de la organización> . crm[ x].dynamics.com", "authenticationType": "AADServicePrincipal", "servicePrincipalId": "<Dienstprinzipal-ID>", "servicePrincipalCredentialType": "ServicePrincipalCert", "servicePrincipalCredential": { "type": "AzureKeyVaultSecret " , " store": { "referenceName": "<AKV-Referenz>", "type": "LinkedServiceReference" }, "secretName": "<Zertifikatname im AKV>" } }, "connectVia": { "referenceName" : "<Nombre de Integration Runtime>", "tipo": "IntegrationRuntimeReference" } } }

Example: Dynamics Online with Office 365 Authentication

{ "name": "DynamicsLinkedService", "properties": { "type": "Dynamics", "typeProperties": { "deploymentType": "Online", "serviceUri": "https://<nombre de la organización> . crm[ x].dynamics.com", "authenticationType": "Office365", "username": "test@contoso.onmicrosoft.com", "contraseña": { "tipo": "SecureString", "valor": " <contraseña >" } }, "connectVia": { "referenceName": "<nombre de Integration Runtime>", "type": "IntegrationRuntimeReference" } }}

Example: Dynamics Online with user-assigned managed identity authentication

{ "name": "DynamicsLinkedService", "properties": { "type": "Dynamics", "typeProperties": { "deploymentType": "Online", "serviceUri": "https://<nombre de la organización> . crm[ x].dynamics.com", "authenticationType": "ManagedIdentity", "credential": { "referenceName": "credential1", "type": "CredentialReference" } }, "connectVia": { "referenceName" : "<Nombre de Integration Runtime>", "tipo": "IntegrationRuntimeReference" } }}

Locations of Dynamics 365 and Dynamics CRM with IFD

Additional features comparable to Dynamics Online arehost nameyporto.

Example: Dynamics On-Premises with IFD with IFD authentication

{ „name“: „DynamicsLinkedService“, „properties“: { „type“: „Dynamics“, „description“: „Dynamics vor ort mit IFD-Linked-Service unter Verwendung der IFD-Authentifizierung“, „typeProperties“: { „ deploymentType“: „ OnPremisesWithIFD", "hostName": "contosodynamicsserver.contoso.com", "port": 443, "organizationName": "admsDynamicsTest", "authenticationType": "Ifd", "username": "test@contoso. onmicrosoft.com", "contraseña": { "tipo": "SecureString", "valor": "<contraseña>" } }, "connectVia": { "referenceName": "<nombre de Integration Runtime>", "tipo ": "IntegrationRuntimeReference" } }}

data set properties

For a complete list of sections and properties available for defining records, seerecordsArticle. This section contains a list of properties supported by the Dynamics dataset.

For copying data to and from Dynamics, the following properties are supported:

example

{ "name": "DynamicsDataset", "properties": { "type": "DynamicsEntity", "schema": [], "typeProperties": { "entityName": "account" }, "linkedServiceName": { "referenceName ": "<Dynamic linked service name>", "type": "linked service reference" } }}

Copy activity properties

For a complete list of sections and properties available for defining activities, seepipelineArticle. This section contains a list of properties supported by Dynamics source and sink types.

Dynamic as font type

To copy data from Dynamics, the copy activityThose onesThe section supports the following properties:

example

"Activities":[ { "Name": "CopyFromDynamics", "type": "Copy", "inputs": [ { "referenceName": "<Dynamics input dataset>", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "<output dataset>", "type": "DatasetReference" } ], "typeProperties": { "source": { "type": "DynamicsSource", "query": " <consulta FetchXML>" }, "sumidouro": { "tipo": "<tipo de coletor>" } } }]

Example of a FetchXML query

<lookup> <entity name="account"> <attribute name="account id" /> <attribute name="name" /> <attribute name="solomarketing" /> <attribute name=" modified" /> <request attribute="modified on" descendant="false" /> <filter type="and"> <condition attribute="modified on" operator="between"> <value>2017-03 -10 18 :40:00z</value> <value>2017-03-12 20:40:00z</value> </condition> </filter> </entity></lookup>

Dynamics as a type of sink

To copy data into Dynamics, the copy activitybathroom sinkThe section supports the following properties:

Dynamics 365 online has a limit of52 concurrent batch calls per organization. If this limit is exceeded, a server busy exception will be thrown before the first request is executed. GuardWriteBatchSizeto 10 or less to avoid concurrent call throttling.

The ideal combination ofWriteBatchSizeythe parallel copyIt depends on your entity schema. Schema elements include the number of columns, the row size, and the number of plug-ins, workflows, or workflow activities associated with these calls. the default value ofWriteBatchSize(10) ×the parallel copy(10) is the recommendation according to Dynamics service. This setting works for most Dynamics entities, although it may not provide the best performance. You can optimize performance by adjusting the combination in your copy activity settings.

example

"Activities":[ { "Name": "Copy to dynamic", "Type": "Copy", "Entries": [ { "Reference name": "<Input record>", "Type": "Record of reference" } ], " output ": [ { "referenceName": "<Dataset of dynamic output>", "type": "DatasetReference" } ], "typeProperties": { "source": { "type ": " <source type>" }, " sink": { "type": "DynamicsSink", "writeBehavior": "Upsert", "writeBatchSize": 10, "ignoreNullValues": true } } }]

see data recovery

To retrieve data from Dynamics views, you must retrieve the view's saved query and use the query to retrieve the data.

There are two entities that store different types of views: the saved query stores the system view and the user query stores the user view. For the display information, see the following FetchXML query and replace "TARGETENTITY" withsaved queryouser query. Each entity type has more attributes available that you can add to the query as needed. learn more aboutstored query entityyuser query entity.

<fetch top="5000" > <entity name="<TARGETENTITY>"> <attribute name="name" /> <attribute name="fetchxml" /> <attribute name="returnedtypecode" /> <attribute name=" Abfragetyp" /> </entity></fetch>

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

<filter type="and" > <condition attribute="return type code" operator="eq" value="1" /> <condition attribute="name" operator="eq" value="Minhas contas ativas" /> </filtro>

Data Type Mapping for Dynamics

If you are copying data from Dynamics, the following table shows the mappings from Dynamics data types to intermediate data types in the service. For information on how a copy activity maps to a source schema and data type to a sink, seeSchema mappings and data types.

Configure the appropriate preliminary data type in a record structure based on its source Dynamics data type using the following mapping table:

Enter data into a lookup field

To write data in a lookup field with multiple targets such as customer and owner, follow this guide and example:

1. Make sure your source includes the field value and the corresponding target entity name.

   • If all records are associated with the same target entity, check one of the following conditions:
     • Your source data has a column that stores the target entity name.
     • Added an additional column in the copy activity source to define the destination entity.
   • If different records are mapped to different target entities, make sure your source data has a column that stores the proper target entity name.

2. Map the entity reference and value columns from the source to the sink. The entity reference column must be mapped to a virtual column with the special naming pattern{Field name} @EntityReference. The column doesn't actually exist in Dynamics. Used to indicate that this column is the metadata column for the specified multi-target lookup field.

Suppose the source has these two columns:

• customer fieldcolumn typeGUID, which is the primary key value of the target entity in Dynamics.
• To watchcolumn typeline, which is the logical name of the target entity.

Also suppose you want to copy this data to the sink's dynamic entity fieldcustomer fieldof the typeIt could.

In mapping columns to copy activities, map the two columns as follows:

• customer fieldancustomer field. This mapping is the normal field mapping.
• To watchancustomer field@entity reference. The sink column is a virtual column that represents the entity reference. Insert these field names into a mapping as they are not displayed when importing schemas.

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

Write data to a lookup field using alternate keys

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

1. Make sure your feed includes all major search columns.

2. Alternate key columns must be mapped to the column with the special naming pattern{lookup_field_name}@{alternate_key_column_name}. Column does not exist in Dynamics. Used to indicate that this column will be used to look up the record in the target entity.

3. Go tocartographyMapping data stream receiver transformation tab. Select the alternative key as the output columns below the search box. The after value specifies the key columns for this alternate key.

   

4. Once selected, alternate key columns will automatically appear below.

   

5. Map your input columns to the output columns on the left.

   

Assign dataflow properties

By transforming data in the mapping data flow, you can read and write to tables in Dynamics. For more information, seefont transformationydecrease in transformationwhen mapping data streams. You can choose to use a Dynamics dataset or aonline registrationas a source and type of sink.

font transformation

The following table lists the properties supported by Dynamics. You can edit these properties infont optionsEyelash.

Dynamics source script example

If you use Dynamics dataset as the source type, the associated dataflow script will be:

source(allowSchemaDrift: true,validateSchema: false,query: '<fetch mapping='logical' count='3 paging-cookie=''><entity name='new_dataflow_crud_test'><attribute name='new_name'/><attribute name='new_releasedate'/></entity></fetch>') ~> DynamicsSource

If you are using an inline dataset, the associated dataflow script is:

source(allowSchemaDrift: true,validateSchema: false,store: 'dynamics',format: 'dynamicsformat',entity: 'Entity1',query: '<fetch mapping='logical' count='3 paging-cookie=''>< Entity name='new_dataflow_crud_test'><attribute name='new_name'/><attribute name='new_releasedate'/></entity></fetch>') ~> DynamicsSource

Sink transformation.

The following table lists the properties supported by Dynamics Receiver. You can edit these properties inrinsing facilitiesEyelash.

Dynamic sink script example

If you use Dynamics dataset as the sink type, the associated dataflow script is:

IncomingStream sumidero(allowSchemaDrift: true, validateSchema: false, deleteable: true, insertable: true, updatable: true, upsertable: true, skipDuplicateMapInputs: true, skipDuplicateMapOutputs: true) ~> DynamicsSink

If you are using an inline dataset, the associated dataflow script is:

sink IncomingStream(allowSchemaDrift: true, validateSchema: false, store: 'dynamic', format: 'dynamicformat', entity: 'Entity1', deleteable: true, insertable: true, updateable: true, upsertable: true, skipDuplicateMapInputs: true, skipDuplicateMapOutputs :wahr) ~> Dynamic Heatsink

Find activity properties

For more information about properties, seeresearch activity.

Next steps

For a list of supported copy activity data stores as sources and sinks, seeCompatible data stores.