How to upsert data into salesforce using data factory?

Question

How to upsert data into salesforce using data factory?

Blasko, Dan W 40

I am trying to perform an upsert into salesforce from data factory.

I am getting this error........

Failure happened on 'Sink' side. ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property '' is invalid: 'ExternalIdFieldName: psu_migration_id__c is required in the source data for upsert mode'.,Source=,''Type=System.ArgumentException,Message=ExternalIdFieldName: psu_migration_id__c is required in the source data for upsert mode,Source=Microsoft.Connectors.Salesforce,'

psu_migration_id__c exist in both the source and the destination table.

Here is my source.......

select id, psu_migration_id__c
from contact
where PSU_Migration_ID__c = '@{item().aqb__contactreferenced__c}'

Blasko, Dan W 40 Reputation points

2025-03-28T15:25:50.9166667+00:00

There was the exact same question asked and no answer was provided.

I don't think the error is on the migration id. I think it's on The value of the property '' is invalid.

The field is defined as external_id in salesforce
J N S S Kasyap 1,630 Reputation points Microsoft External Staff

2025-03-28T18:04:58.6433333+00:00

Hi @Blasko, Dan W

To upsert data into Salesforce from Azure Data Factory (ADF) but your encountering the following error:

ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property '' is invalid: 'ExternalIdFieldName: psu_migration_id__c is required in the source data for upsert mode'.

This error means that Salesforce expects the External ID field (psu_migration_id__c) to be present in the incoming data, but ADF is unable to find it correctly.

It looks like the upsert operation in Salesforce is failing because ADF cannot find the psu_migration_id__c field in the source data. Even though you have included it in your SQL query, Salesforce requires it to be correctly passed in the pipeline.

Salesforce field names are case-sensitive. The field name in SQL might be PSU_Migration_ID__c, but ADF expects psu_migration_id__c. Ensure that the exact casing is used in SQL and ADF column mapping.

Even though the field exists in the source query, it might not be mapped correctly in ADF’s Mapping section. Go to Copy Activity > Mapping Tab and explicitly map psu_migration_id__c from source to target.

Salesforce requires the External ID field to be defined properly. If it's missing or not marked as an External ID, upsert will fail.

If the SQL query is dynamically generated (@{item().aqb__contactreferenced__c}), it may be returning empty or incorrect values.Debug the pipeline by logging the exact query output and verifying that psu_migration_id__c is populated.

Please refer the below Microsoft Document
https://learn.microsoft.com/en-us/azure/data-factory/connector-salesforce?tabs=data-factory

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Blasko, Dan W 40 Reputation points

2025-03-28T18:44:52.07+00:00

When i look at the output, i can see where it reads 1 row from salesforce. But it doesn't copy any rows.
J N S S Kasyap 1,630 Reputation points Microsoft External Staff

2025-03-29T00:52:24.28+00:00

@Blasko, Dan W

Based on the issue with the upsert operation in Azure Data Factory (ADF) to Salesforce, where the output shows rowsRead: 1 but rowsCopied: 0, and considering the error about the ExternalIdFieldName: psu_migration_id__c, here are major points to focus on for troubleshooting and resolution

Enable fault tolerance in the ADF Copy Activity ("Skip and log incompatible rows") to capture the detailed error response from Salesforce. Check the logs in the specified storage account for specific reasons why the row wasn’t copied, such as validation errors or missing required fields beyond the External ID.

Ensure the data type of psu_migration_id__c in the SQL source matches the data type in Salesforce (e.g., Text vs. Number). A mismatch can cause Salesforce to reject the upsert. Use a Derived Column activity in ADF to cast the source data to the correct type if needed.

Ensure the psu_migration_id__c values are free of special characters, spaces, or formatting issues that might cause Salesforce to reject the upsert. Add a Derived Column activity in ADF to clean the data (e.g., trim whitespace and remove non-alphanumeric characters) before the Copy Activity.

Check if the Salesforce connector in ADF is using an outdated API version or has known bugs. Update to the latest connector version or specify a newer Salesforce API version (e.g., v59.0 as of early 2025) in the ADF Salesforce dataset settings, then rerun the pipeline.

I hope this information helps. Please do let us know if you have any further queries.
Aswin 392 Reputation points

2025-03-30T01:15:20.3133333+00:00

Hi Blasko, Dan W, the error message says that the ExternalIdFieldName: psu_migration_id__c has atleast one empty value or null value in your source. For upsert operation, it can't have any null values. Check once, if you have any such rows in your source data.
J N S S Kasyap 1,630 Reputation points Microsoft External Staff

2025-03-31T00:47:30.2+00:00

@Blasko, Dan W
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Rakesh Govindula 965 Reputation points Microsoft External Staff

2025-04-01T05:53:33.1533333+00:00

@Blasko, Dan W,

You have mentioned that it is reading one row but not copying. Can you use the same query in a lookup activity and cross-check whether the obtained row exists or not in your target Salesforce table?
Rakesh Govindula 965 Reputation points Microsoft External Staff

2025-04-02T11:10:06.9966667+00:00

@Blasko, Dan W We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Rakesh Govindula 965 Reputation points Microsoft External Staff

2025-04-03T11:21:20.45+00:00

@Blasko, Dan W We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Blasko, Dan W 40 Reputation points

2025-03-28T15:25:50.9166667+00:00

There was the exact same question asked and no answer was provided.

I don't think the error is on the migration id. I think it's on The value of the property '' is invalid.

The field is defined as external_id in salesforce
Blasko, Dan W 40 Reputation points

2025-03-28T18:44:52.07+00:00

When i look at the output, i can see where it reads 1 row from salesforce. But it doesn't copy any rows.
J N S S Kasyap 1,630 Reputation points Microsoft External Staff

2025-03-29T00:52:24.28+00:00

@Blasko, Dan W

Based on the issue with the upsert operation in Azure Data Factory (ADF) to Salesforce, where the output shows rowsRead: 1 but rowsCopied: 0, and considering the error about the ExternalIdFieldName: psu_migration_id__c, here are major points to focus on for troubleshooting and resolution

Enable fault tolerance in the ADF Copy Activity ("Skip and log incompatible rows") to capture the detailed error response from Salesforce. Check the logs in the specified storage account for specific reasons why the row wasn’t copied, such as validation errors or missing required fields beyond the External ID.

Ensure the data type of psu_migration_id__c in the SQL source matches the data type in Salesforce (e.g., Text vs. Number). A mismatch can cause Salesforce to reject the upsert. Use a Derived Column activity in ADF to cast the source data to the correct type if needed.

Ensure the psu_migration_id__c values are free of special characters, spaces, or formatting issues that might cause Salesforce to reject the upsert. Add a Derived Column activity in ADF to clean the data (e.g., trim whitespace and remove non-alphanumeric characters) before the Copy Activity.

Check if the Salesforce connector in ADF is using an outdated API version or has known bugs. Update to the latest connector version or specify a newer Salesforce API version (e.g., v59.0 as of early 2025) in the ADF Salesforce dataset settings, then rerun the pipeline.

I hope this information helps. Please do let us know if you have any further queries.
Aswin 392 Reputation points

2025-03-30T01:15:20.3133333+00:00

Hi Blasko, Dan W, the error message says that the ExternalIdFieldName: psu_migration_id__c has atleast one empty value or null value in your source. For upsert operation, it can't have any null values. Check once, if you have any such rows in your source data.
J N S S Kasyap 1,630 Reputation points Microsoft External Staff

2025-03-31T00:47:30.2+00:00

@Blasko, Dan W
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Rakesh Govindula 965 Reputation points Microsoft External Staff

2025-04-01T05:53:33.1533333+00:00

@Blasko, Dan W,

You have mentioned that it is reading one row but not copying. Can you use the same query in a lookup activity and cross-check whether the obtained row exists or not in your target Salesforce table?
Rakesh Govindula 965 Reputation points Microsoft External Staff

2025-04-02T11:10:06.9966667+00:00

@Blasko, Dan W We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Rakesh Govindula 965 Reputation points Microsoft External Staff

2025-04-03T11:21:20.45+00:00

@Blasko, Dan W We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Aswin 392

Hi Blasko, Dan W
The error message says that the ExternalIdFieldName: psu_migration_id__c has atleast one empty value or null value in your source. For upsert operation, it can't have any null values. Check once, if you have any such rows in your source data.

Blasko, Dan W 40 Reputation points

2025-04-03T12:38:46.2133333+00:00

it does not contain any nulls. I also have "ignore null values" checked on the sync tab.
AnnuKumari-MSFT 34,456 Reputation points Microsoft Employee

2025-04-07T10:48:39.62+00:00

Blasko, Dan W ,

What is the datasource from where you are pulling the data and copying to salesforce?
Blasko, Dan W 40 Reputation points

2025-04-07T11:03:32.0033333+00:00

I have a 6 column table in oracle that I need to get into salesforce. 4 of the 6 columns map and insert with no issue. I have a contactreference column and an accountreference column in the source table. These two columns contain the value that matches the psu_migration_id in the contact and account table respectively.

If this wasn't salesforce, I would just do a simple update, but soql in salesforce is terrible and doesn't support update statements.

update adf_alternateid_c a

set contactreference = (select c.id from contact c where c.psu_migrationid = a.contactreference)

So I want to match the current value in contactreference to psu_migrationid in the contact table. But then I want to update the contactreference = the ID column from the contact table.
AnnuKumari-MSFT 34,456 Reputation points Microsoft Employee

2025-04-09T05:49:50.6266667+00:00

Blasko, Dan W

Could you please share the ADF pipeline details, screenshot and pipeline json. You can make use of mapping tab to map the columns correctly between source and destination .

Share via

How to upsert data into salesforce using data factory?

1 answer

Your answer