Copy Activity to Salesforce Sink failure when updating a field referenced through a lookup relationship

Salman Anza 0

Hello All,

I have a Copy Activity defined whose source is a CSV and target is a custom Salesforce sObject.

As part of the mapping, I need to update a custom field on the standard Account sObject, which is referenced by the custom sObject through a lookup relationship field.

The Relevant Schemas for the two sObjects are: sinkCustomSalesforceObj.Lookup_Relationship_to_Account__r, Account.ID_In_Account__c

My first thought is to reference the field through the lookup relation field:

{
    "inputs": [
        {
            "referenceName": "sourceCSV",
            "type": "DatasetReference"
        }
    ],
    "outputs": [
        {
            "referenceName": "sinkCustomSalesforceObj",
            "type": "DatasetReference"
        }
    ],
	"typeProperties": {
        "source": {
            "type": "DelimitedTextSource",
            "storeSettings": {
                "type": "SftpReadSettings",
                "recursive": true,
                "enablePartitionDiscovery": false,
                "disableChunking": false
            },
            "formatSettings": {
                "type": "DelimitedTextReadSettings"
            }
        },
        "sink": {
            "type": "SalesforceSink",
            "writeBatchSize": 5000,
            "writeBehavior": "upsert",
            "externalIdFieldName": "external_Id__c", //i.e. sinkCustomSalesforceObj.external_Id__c
            "ignoreNullValues": false
        },
        "enableStaging": false,
        "translator": {
            "type": "TabularTranslator",
            "mappings": [
				...,
                {
                    "source": {
                        "name": "ColumnFromCSV"
                    },
                    "sink": {
                        "name": "Look_Up__r.ID_In_Account__c"
                    }
                }
            ],
            "typeConversion": true,
            "typeConversionSettings": {
                "allowDataTruncation": true,
                "treatBooleanAsNumber": false
            }
        }
    }
}

However, this results in the following error:

ErrorCode=SalesforceOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Salesforce Operation Failed: INVALID_FIELD:Foreign key external ID: 12345678 not found for field ID_In_Account__c in entity Account:--,Source=Microsoft.DataTransfer.Runtime.SalesforceConnector,'

The Salesforce specific docs doe not mention how to deal with this issue.

Does anyone have any suggestions?

Regards

Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2023-10-06T13:07:48.42+00:00
@Salman Anza Thank you for reaching out with your query. Based on the error message you provided, it seems that the external ID value is not found for the field ID_In_Account__c in the Account entity. This error can occur when the external ID value is not present in the target Salesforce org or if the external ID field is not set up correctly.
To resolve this issue, you can try the following steps:

Verify that the external ID field is set up correctly in the target Salesforce org. You can refer to the Salesforce documentation for more information on how to set up external ID fields.

Ensure that the external ID value is present in the target Salesforce org. You can check this by querying the Account entity in the target Salesforce org using the external ID value.

If the external ID value is not present in the target Salesforce org, you can try updating the CSV file to include the missing external ID value. Alternatively, you can use the Salesforce Upsert API to insert the missing record with the external ID value.

If the issue persists, you can try enabling debug logging for the Salesforce connector in Azure Data Factory to get more detailed error messages.

I hope this helps! Let me know if you have any further questions or concerns.
Salman Anza 0 Reputation points

2023-10-06T21:06:30.2533333+00:00

Hello Smaran,

The external ID only exists in the parent (sinkCustomSalesforceObj). The child (Account) does not have an external ID field named external_Id__c.

Does ADF have the capability to update a field on a child record through reference from the parent?

(i.e. upsert on Parent_c.child_ref__r.field_in_child__c?)
Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2023-10-09T12:34:35.8333333+00:00
@Salman Anza Thank you for providing additional information. Based on your clarification, it seems that the external ID only exists in the parent object (sinkCustomSalesforceObj) and not in the child object (Account). You are trying to update a field on the child object through a reference from the parent object.

To answer your question, yes, Azure Data Factory (ADF) has the capability to update a field on a child record through a reference from the parent record. However, you need to ensure that the reference is set up correctly in the mapping.
In your case, you can try updating the mapping to reference the child field directly instead of using the lookup relationship field. Here's an example of how you can update the mapping:

{ "source": { "name": "ColumnFromCSV" }, "sink": { "name": "Account.ID_In_Account__c" } }

This mapping will update the ID_In_Account__c field on the Account object directly using the value from the ColumnFromCSV field in the source dataset.

Alternatively, you can try using the Salesforce Lookup activity in ADF to retrieve the child record based on the external ID value in the parent record. Once you have retrieved the child record, you can update the required field using the Salesforce Update activity.

I hope this helps! Let me know if you have any further questions or concerns.

Salman Anza 0

Hello Smaran,

I still have not been able to get the copy activity to work. All attempts have resulted in a variation of the error:

ErrorCode=SalesforceCannotFindExternalIdField,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=[SPECIFIED EXTERNAL ID FOR CURRENT ATTEMPT] externalIdFieldName cannot be found in DataReader.,Source=Microsoft.DataTransfer.Runtime.SalesforceConnector,'

I have made four attempts. For each attempt, the sink dataset (points to the parent sObject) and mapping are the same. What's different are the external Ids:

Attempt 1, external id in parent

{ "sink": { 	"type": "SalesforceSink", 	"writeBatchSize": 5000, 	"writeBehavior": "upsert", 	"externalIdFieldName": "external_Id_IN_PARENT__c", 	"ignoreNullValues": false }, "enableStaging": false, "translator": { 	"type": "TabularTranslator", 	"mappings": [ 		{ 			"source": { 				"name": "ColumnFromCSV" 			}, 			"sink": { 				"name": "Account.ID_In_Account__c" 			} 		} 	], }

Attempt 2, external id set to child id field (i.e. Account.id)

"sink": { 	"type": "SalesforceSink", 	"writeBatchSize": 5000, 	"writeBehavior": "upsert", 	"externalIdFieldName": "Account.Id", 	"ignoreNullValues": false }, "enableStaging": false, "translator": { 	"type": "TabularTranslator", 	"mappings": [ 		{ 			"source": { 				"name": "ColumnFromCSV" 			}, 			"sink": { 				"name": "Account.ID_In_Account__c" 			} 		} 	], }

Attempt 3, external id set to lookup field in parent, using __c

"sink": { 	"type": "SalesforceSink", 	"writeBatchSize": 5000, 	"writeBehavior": "upsert", 	"externalIdFieldName": "Look_Up__c", 	"ignoreNullValues": false }, "enableStaging": false, "translator": { 	"type": "TabularTranslator", 	"mappings": [ 		{ 			"source": { 				"name": "ColumnFromCSV" 			}, 			"sink": { 				"name": "Account.ID_In_Account__c" 			} 		} 	], }

Attempt 4, external id set to lookup field in parent, using __r

"sink": { 	"type": "SalesforceSink", 	"writeBatchSize": 5000, 	"writeBehavior": "upsert", 	"externalIdFieldName": "Look_Up__r", 	"ignoreNullValues": false }, "enableStaging": false, "translator": { 	"type": "TabularTranslator", 	"mappings": [ 		{ 			"source": { 				"name": "ColumnFromCSV" 			}, 			"sink": { 				"name": "Account.ID_In_Account__c" 			} 		} 	], }

Is there something I am doing wrong?

Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2023-10-10T15:52:45.3166667+00:00

@Salman Anza We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
Salman Anza 0 Reputation points

2023-10-10T16:39:39.9333333+00:00

Sure thing Smaran!
Salman Anza 0 Reputation points

2023-10-17T16:24:21.4333333+00:00

Hello @Smaran Thoomu

Were you able to get more info from the internal team?
Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2023-10-18T15:12:15.29+00:00
@Salman Anza I apologize for the delayed response. Upon consulting with our internal team, it has been suggested to check a few additional things:

Make sure that the external ID field in the parent object is marked as an external ID field. You can check this by going to the field definition in Salesforce and looking for the External ID checkbox.

Make sure that the user or service account running the copy activity has access to the external ID field. You can check this by going to the user or service account permissions and looking for the Read and Write permissions for the external ID field.

Make sure that the external ID values in the CSV file are in the correct format. Salesforce external ID values are typically 18-character alphanumeric strings.

If the issue continues, you may want to consider raising a support ticket for a more in-depth investigation.
Salman Anza 0 Reputation points

2023-10-19T21:45:21.4666667+00:00

Hello Smaran,

My implementation has all three points you mentioned.

It would appear I need to create a support ticket.

Thanks for all your support!

1 answer

Salman Anza 0 Reputation points

2023-10-19T21:45:52.4133333+00:00

I will create a support ticket with MS
Please sign in to rate this answer.
Smaran Thoomu 17,120 Reputation points Microsoft Vendor

2023-10-20T06:14:46.2566667+00:00

@Salman Anza Thank you for the update. Could you please share the SR# with us so that we can track it internally?

Braulio Cavieres 0 Reputation points

2024-01-31T22:27:08.7166667+00:00

Hola, Tengo el mismo problema. se logro solucionar? @Salman Anza

FERNANDO BASAEZ 20 Reputation points

2024-07-10T06:06:10.2366667+00:00

I got the same Issue, could you please write a feedback how to solve this? @Smaran Thoomu @Salman Anza
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Copy Activity to Salesforce Sink failure when updating a field referenced through a lookup relationship

1 answer

Your answer