Azure Datafactory Upload to Salesforce and reference field on another object

Question

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.

Answer 1

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 Account.Ext_UID__c 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