通过ADF将记录加载到Dynamics 365中 [英] Loading records into Dynamics 365 through ADF

问题描述

我正在使用Azure数据工厂中的Dynamics连接器.

I'm using the Dynamics connector in Azure Data Factory.

此连接器是否支持加载需要传入父记录键的子记录?例如，如果我要创建一个contact并将其附加到父级account，我会添加一个空值contactid，有效的parentcustomerid GUID的记录，并将parentcustomeridtype设置为1(或2)，但是我收到错误消息.

Does this connector support loading child records which need a parent record key passed in? For example if I want to create a contact and attach it to a parent account, I upsert a record with a null contactid, a valid parentcustomerid GUID and set parentcustomeridtype to 1 (or 2) but I get an error.

我已成功连接到Dynamics 365，并将数据(例如lead表)提取到SQL Server表中

I'm successfully connecting to Dynamics 365 and extracting data (for example, the lead table) into a SQL Server table

为了测试是否可以通过其他方式传输数据，我只是将数据从lead表加载回Dynamics中的lead实体中.

To test that I can transfer data the other way, I am simply loading the data back from the lead table into the lead entity in Dynamics.

我收到此错误:

接收器"侧发生故障. ErrorCode = DynamicsMissingTargetForMultiTargetLookupField，'Type = Microsoft.DataTransfer.Common.Shared.HybridDeliveryException，Message =，Source =，''Type = Microsoft.DataTransfer.Common.Shared.HybridDeliveryException，Message =找不到多目标查找字段的目标列:"ownerid".

Failure happened on 'Sink' side. ErrorCode=DynamicsMissingTargetForMultiTargetLookupField,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot find the target column for multi-target lookup field: 'ownerid'.

作为测试，我从加载成功的源列列表中删除了ownerid.

As a test I removed ownerid from the list of source columns it loads OK.

这显然是外键值.

这对我提出了两个问题:

It raises two questions for me:

1. 特别是关于错误消息的信息:如果我知道需要使用哪个查询，如何指定应该针对哪个查询表进行验证? ADF连接器中没有设置允许我执行此操作.

1. Specifically with regards to the error message: If I knew which lookup it needed to use, how can I specify which lookup table it should validate against? There's no settings in the ADF connector to allow me to do this.

这显然是外键值.如果我只有该行的名称(或业务密钥)，如何轻松查找外键值?

This is obviously a foreign key value. If I only had the name (or business key) for this row, how can I easily lookup the foreign key value?

通常如何通过其他API(即网络API)完成此操作?

How is this normally done through other API's, i.e. the web API?

是否有XRMToolbox加载项可以帮助澄清?

Is there an XRMToolbox addin that would help clarify?

我还阅读了一些帖子，暗示您可以在XML文档中发送预连接的数据，因此也许也有帮助.

I've also read some posts that imply that you can send pre-connected data in an XML document so perhaps that would help also.

我意识到我的源数据集中的lead.ownertypeid字段是NULL(即已导出的内容).如果我在各种Xrmtoolbox工具中浏览它，则它也为NULL.我尝试将其硬编码为systemuser(这是owner表中与实际所有者记录相对应的内容)，但仍然出现相同的错误.

I realised that the lead.ownertypeid field in my source dataset is NULL (that's what was exported). It's also NULL if I browse it in various Xrmtoolbox tools. I tried hard coding it to systemuser (which is what it actually is in the owner table against the actual owner record) but I still get the same error.

我还注意到systemuser表中有一条记录具有相同的PK值

I also notice there's a record with the same PK value in systemuser table

因此，同一记录在两个表中，但是如何告诉动态连接器使用哪个?为什么还要在乎呢?

So the same record is in two tables, but how do I tell the dynamics connector which one to use? and why does it even care?

对于customerid，我也收到了与msauto_testdrive类似的消息.

I was getting a similar message for msauto_testdrive for customerid.

我用customerid=null排除了所有记录，并得到了相同的错误.

I excluded all records with customerid=null, and got the same error.

此链接似乎表明我需要将customeridtype设置为1(帐户)或2(联系人).我这样做了，但仍然遇到相同的错误.

This link appears to indicate that I need to set customeridtype to 1 (Account) or 2 (Contact). I did so, but still got the same error.

我还相信我有与此人相同的问题.

也许ADF连接器也遇到同样的问题.

Maybe the ADF connector suffers from the same problem.

推荐答案

这是关于CDS多态查询(如客户和所有者)的ADF限制. 对此赞ADF想法

This is the ADF limitation with respect to CDS polymorphic lookups like Customer and Owner. Upvote this ADF idea

解决方法是使用两个临时源查找字段(如果是所有者，则是所有者团队和用户，如果是客户，则是客户和联系人)，并在MS Flow中使用并行分支来解决此问题. 了解更多，您也可以下载Flow示例以供使用.

Workaround is to use two temporary source lookup fields (owner team and user in case of owner, account and contact in case of customer) and with parallel branch in a MS Flow to solve this issue. Read more, also you can download the Flow sample to use.

• 首先，在要将客户查询数据导入到实体中的实体上分别创建两个临时查询字段，分别到客户实体和联系人实体
• 然后，在ADF管道流程中，您需要将客户"和联系人"字段的GUID值映射到上面创建的相应查找字段.最简单的方法是在源数据集中有两列-一列包含要映射的客户GUID，另一列包含Contact.
• 然后，最后，您可以将Microsoft Flow组合在一起，然后执行从临时字段到客户"查找字段的适当映射.首先，定义何时创建受影响的实体记录(在本例中为Contact)的触发点，并添加一些并行分支以检查这两个临时查找字段中的任何一个值

• First, create two temporary lookup fields on the entity that you wish to import Customer lookup data into it, to both the Account and Contact entities respectively
• Within your ADF pipeline flow, you will then need to map the GUID values for your Account and Contact fields to the respective lookup fields created above. The simplest way of doing this is to have two separate columns within your source dataset – one containing Account GUID’s to map and the other, Contact.
• Then, finally, you can put together a Microsoft Flow that then performs the appropriate mapping from the temporary fields to the Customer lookup field. First, define the trigger point for when your affected Entity record is created (in this case, Contact) and add on some parallel branches to check for values in either of these two temporary lookup fields

• 然后，如果满足以下任一条件，则设置更新记录任务以执行单个字段更新，如下所示(如果ADF帐户查找字段中包含数据)

• Then, if either of these conditions is hit, set up an Update record task to perform a single field update, as indicated below if the ADF Account Lookup field has data within it

这篇关于通过ADF将记录加载到Dynamics 365中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！