Azure Data factory Upsert Not working With only Edit Permissions in Salesforce RRS feed

  • Question

  • Hello, 

    I have an automated job in Azure Data factory which uses Upsert function in the copy data activity to update records in an salesforce object. The user profile used to connect to salesforce has object level Edit, read access but does not have create access. 

    The job was working successfully Until 10/18 when it failed for the first time. Now, in order for the job to process successfully i am having to provide Create access to the user profile in salesforce. This is troublesome as it could not create new records in the object which is not acceptable for our Org. 

    So my question, Why did the Upsert behaviour change suddenly? Why is it now requiring both Create and Edit permissions on salesforce to be successfully processed. Below is the error which i am seeing lately - 

    { "errorCode": "2200", "message": "Failure happened on 'Sink' side. ErrorCode=UserErrorSalesforceOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=[InvalidJob]No create/update access for object:Account,Source=Microsoft.DataTransfer.Runtime.SalesforceConnector,'", "failureType": "UserError", "target": "AzuretoSFDC_Write" }

    Wednesday, October 23, 2019 8:14 PM

All replies

  • Hello Karthik Dulam and thank you for your question.

    Upsert sometimes updates (edit) records, and sometimes inserts (create) records.  Is it possible that it only updated/edited records before 10/18 and created a record for the first time on 10/18?

    Friday, October 25, 2019 12:56 AM
  • I checked the data and confirmed that it did not create new records as part of that run and also did not update the values on the existing records. The job itself is failing as it says that there is no access to the object but the same job completes successfully if we assign Create permissions for the object on SFDC. We never had create permissions on the objects previously so this is something that stopped working recently. 

    Also, it did not try to create a new record on 10/18. All the records in the run were existing records. 

    Monday, October 28, 2019 3:32 PM
  • I will inquire internally whether anything changed on 10/18.  There is also the possibility that Salesforce changed its policy.  While I am checking with the Data Factory team, could you check with Salesforce?
    Monday, October 28, 2019 6:40 PM
  • Kathik Dulam, could you please share with me the pipeline run IDs of an affected pipeline?
    It would be best if you could share the IDs for, 2 successful runs from before 10/18, and 2 failed runs for comparison. 
    Wednesday, October 30, 2019 12:02 AM
  • This Pipeline only runs on Fridays

    Failed Pipeline Run: 10/30 (Triggered Manually Today ended with same issue)

    Pipeline run ID: b5b68ba7-5621-4ba9-8ca5-5ed50dd035cf

    Failed Pipeline Run: 10/18

    Pipeline run ID3e708de3-bed2-4507-a0e8-7b1c9874e574

    Copy activity AzuretoSFDC_Write fails with the Error stated. 


    Successful Pipeline Run: 10/11

    Pipeline run ID41b94c00-92e3-4a4d-851f-04edb5f7317a

    All Activities succeeded. 

    Successful Pipeline Run: 09/20

    Pipeline run ID65da3e8f-89cb-4e01-af47-090154fb8295

    Succesful Pipeline Run: 09/27

    Pipeline run IDb534df86-aaa7-4646-858a-f2c7e6bbc87c

    Wednesday, October 30, 2019 7:25 PM
  • Thank you for the response Karthik Dulam.  I have submitted these for review.
    Thursday, October 31, 2019 12:53 AM
  • I heard back, and no anomalies were found.  Just to confirm, the entire time, only edit and read permissions were applied?  There is no possibility that previously it had create permissions?
    Thursday, October 31, 2019 7:20 PM
  • Thanks for the reply, Martin. Yes, I can confirm that it only had Read and Edit permissions since that is enforced at the Organization level. 

    Only after the jobs started failing we tried assigning Create authority and the pipeline completed with no issues after the Create authority. I am also checking with salesforce but based on my initial discussions with them, nothing changed from their end as well. 

    Thursday, October 31, 2019 8:04 PM
  • I have more information.

    By design, "create" permission is required to insert new rows.  Defined here.

    Maybe  instead of creating rows previously, it could have been editing null rows.

    Monday, November 4, 2019 11:21 PM
  • Hello Martin, 

    Thanks for your Note. So, in our particular use case, i can confirm you that we have never been creating new rows. We use the upsert operation with external ID as a mode to update the data in already existing rows in Salesforce. Salesforce does not enforce both Create & edit permissions for Upsert operation, they only say user should have either create or edit (not create and edit) for the upsert to work.  

    I can confirm that in all the runs we were only updating the existing rows in salesforce. We did a debug on the salesforce end after running the pipeline and the call never triggered salesforce. So our assumption is that Azure Data Factory is checking/enforcing both create and edit permissions in salesforce for the user profile before actually making the upsert request to SFDC.

    Can we confirm if Azure DF is indeed checking for the permissions first before triggering the call for upsert ? Again, this issue came into light because suddenly our jobs started failing and only way they are successful again is by granting a Create authority to the objects in salesforce which we never had. Even after assigning the create authority we are only editing the existing rows and not actually creating new ones. Thanks for your help in looking into this. 

    Tuesday, November 5, 2019 2:15 PM
  • Data Factory does not pre-check the permissions in Salesforce.  You can verify this by checking whether Salesforce has received a request from Data Factory to get permissions info.  If ADF never requests permissions info, how can ADF know what it is allowed to do?

    Was the external id ever null?

    Thursday, November 7, 2019 8:03 AM