Azure Datafactory上传到Salesforce并在另一个对象上引用字段

问题描述

这是场景-我们正在使用Azure DataFactoryV2运行一系列管道，这些管道从本地数据源获取帐户数据，进行转换并将其上传到Salesforce.

当前，我们要导入一个帐户并将其与另一个帐户相关联.帐户具有标准的父/子关系(例如，转销商帐户和子帐户)，并且使用内部Salesforce ID.

我们还使用外部ID进行Upserting，并且该ID对于每条记录都是唯一的.

根据SF文档:

对于自定义查找/主从字段，如果字段API名称为 Account__c ，则可以将其映射为 Account__r.Ext_UID__c 进行上报.

请确保在子帐户引用它们之前先存在父帐户...也许您在以前的工作中加载了它们，也许最好分两个阶段加载它:

1. 所有不带父级映射的帐户的平面插入/向上插入
2. 重新生成仅映射此记录的ext ID和父级的ext的更新/更新.id

This is the scenario - we are using Azure DataFactoryV2 to run a series of pipelines that take account data from a local datasource, transform it and upload into Salesforce.

Currently we are wanting to Import an Account and relate it to another account. There is the standard Parent/Child relationship for Accounts (eg a Reseller account and a child account) and this is using the internal Salesforce ID.

We are also using an External ID for the purpose of Upserting and this ID is unique to each record.

According to SF Documentation: Here - when you manually call the API and pass it a JSON file, you are able to add a relationship within the JSON:

    {
   "Name" : "NewAccount",
   "account__r" :
   {
       "Ext_UID__c" : 123
   }

}

However, this doesn't appear to be doable in Azure DataFactoryV2 to specify a lookup relationship in the code, or if it is, I'm not sure how to do it.

For reference - here is the Pipeline JSON code:

{
"name": "Import_to_Salesforce",
"properties": {
    "activities": [
        {
            "name": "Load_to_Salesforce",
            "type": "Copy",
            "policy": {
                "timeout": "7.00:00:00",
                "retry": 0,
                "retryIntervalInSeconds": 30,
                "secureOutput": false,
                "secureInput": false
            },
            "userProperties": [
                {
                    "name": "Source",
                    "value": "[dbo].[Account]"
                },
                {
                    "name": "Destination",
                    "value": "Account"
                }
            ],
            "typeProperties": {
                "source": {
                    "type": "SqlSource"
                },
                "sink": {
                    "type": "SalesforceSink",
                    "writeBatchSize": 5000,
                    "writeBehavior": "upsert",
                    "externalIdFieldName": "Ext_UID__c",
                    "ignoreNullValues": false
                },
                "enableStaging": false,
                "enableSkipIncompatibleRow": true,
                "dataIntegrationUnits": 0,
                "translator": {
                    "type": "TabularTranslator",
                    "columnMappings": {
                        "Name": "Name",
                        "ParentId": "ParentId",
                        "BillingStreet": "BillingStreet",
                        "BillingCity": "BillingCity",
                        "BillingPostalCode": "BillingPostalCode",
                        "BillingCountry": "BillingCountry",
                        "ShippingStreet": "ShippingStreet",
                        "ShippingCity": "ShippingCity",
                        "ShippingPostalCode": "ShippingPostalCode",
                        "ShippingCountry": "ShippingCountry",
                        "Phone": "Phone",
                        "AccountNumber": "AccountNumber",
                        "Brand__c": "Brand__c",
                        "Account_Status__c": "Account_Status__c",
                        "Account_Type__c": "Account_Type__c",
                        "Preferred_Payment_Method__c": "Preferred_Payment_Method__c",
                        "Last_Account_Login__c": "Last_Account_Login__c",
                        "Ext_UID__c": "Ext_UID__c",
                        "Auto_Renew_Status__c": "Auto_Renew_Status__c",
                        "Account_Balance__c": "Account_Balance__c",
                        "Outstanding_Amount_30_days__c": "Outstanding_Amount_30_days__c",
                        "Outstanding_Amount_60_days__c": "Outstanding_Amount_60_days__c",
                        "Outstanding_Amount_90_days__c": "Outstanding_Amount_90_days__c",
                        "Account_Priority__c": "Account_Priority__c",
                        "Reseller__c": "Reseller__c",
                        "Last_Payment__c": "Last_Payment__c"
                    }
                }
            },
            "inputs": [
                {
                    "referenceName": "Staging_Source",
                    "type": "DatasetReference"
                }
            ],
            "outputs": [
                {
                    "referenceName": "Destination_Load_to_Salesforce",
                    "type": "DatasetReference"
                }
            ]
        }
    ]
},
"type": "Microsoft.DataFactory/factories/pipelines"}

Any input would be greatly appreciate.

解决方案

Which SF connector you're using? If there's no meaningful name look at your API user's login history in Salesforce. It's probably listed as "Simba Technologies" or something like that?

For standard relationships you should be able to just type Parent.Ext_UID__clike in this Contact load:

For custom lookups/master-detail fields if the field API name is Account__c you can map it for upsert as Account__r.Ext_UID__c.

Just make sure that parent accounts exist before child accounts reference them... Maybe you loaded them in previous job, maybe it's best to load it in two stages:

1. Flat insert/upsert of all accounts without parent mapped
2. Reparenting update/upsert that maps only this record's ext id and the parent's ext. id

这篇关于Azure Datafactory上传到Salesforce并在另一个对象上引用字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！