Trying to Understand External IDs with tSalesforceOutput

[Joel2]

Hi Everyone,

We are in the process of implementing Talend, and I'm working on migrating our existing Salesforce integration. The tool we used previously had very strong Salesforce support, but Talend seems to be a bit trickier.

As a simple example of trying to get used to this, I am only working with a single object, Account, and trying to upsert records that includes updating the ParentId field. We have an external ID "SIS_ID__c" defined on the object. I am trying to import from a database that contains the following fields:

• SIS_ID__c (the UPSERT key field)
• ParentId__EID (external ID of the parent Account record)
• Name

There are some other fields that are not important for the example. The __EID suffix is our convention to indicate that the value is actually a an external ID lookup for the target of the lookup.

It seems to me that this should all be able to happen in the tSalesforceOutput object. Here is what I have mapped there:

I've been over the documentation and any examples that I can find and I believe that I have gotten the mapping correct. ParentID__EID is in field in the source that is looked up in Account.SIS_ID__c and store the ID that was found into ParentId:

Running this, though, generates the following error:

[FATAL] 22:01:08 cwru_ipaas_talend_codebase.sf_base_academic_0_1.SF_Base_Academic- tSalesforceOutput_2 com.sforce.ws.SoapFaultException: Unexpected element {urn:sobject.partner.soap.sforce.com}type during simple type deserialization
java.io.IOException: com.sforce.ws.SoapFaultException: Unexpected element {urn:sobject.partner.soap.sforce.com}type during simple type deserialization
	at org.talend.components.salesforce.runtime.SalesforceWriter.doUpsert(SalesforceWriter.java:457) ~[components-salesforce-runtime-0.28.2.jar:?]
	at org.talend.components.salesforce.runtime.SalesforceWriter.logout(SalesforceWriter.java:678) ~[components-salesforce-runtime-0.28.2.jar:?]
	at org.talend.components.salesforce.runtime.SalesforceWriter.close(SalesforceWriter.java:662) ~[components-salesforce-runtime-0.28.2.jar:?]
	at org.talend.components.salesforce.runtime.SalesforceWriter.close(SalesforceWriter.java:66) ~[components-salesforce-runtime-0.28.2.jar:?]
	at cwru_ipaas_talend_codebase.sf_base_academic_0_1.SF_Base_Academic.tDBInput_2Process(SF_Base_Academic.java:4864) [classes/:?]
	at cwru_ipaas_talend_codebase.sf_base_academic_0_1.SF_Base_Academic.tDBInput_1Process(SF_Base_Academic.java:2884) [classes/:?]
	at cwru_ipaas_talend_codebase.sf_base_academic_0_1.SF_Base_Academic.tSalesforceConnection_1Process(SF_Base_Academic.java:844) [classes/:?]
	at cwru_ipaas_talend_codebase.sf_base_academic_0_1.SF_Base_Academic.tDBConnection_1Process(SF_Base_Academic.java:596) [classes/:?]
	at cwru_ipaas_talend_codebase.sf_base_academic_0_1.SF_Base_Academic.runJobInTOS(SF_Base_Academic.java:5688) [classes/:?]
	at cwru_ipaas_talend_codebase.sf_base_academic_0_1.SF_Base_Academic.main(SF_Base_Academic.java:5470) [classes/:?]

The error could definitely use some clarification. We made some changes in the tSalesforceOutput schema by changing the highlighted name to the target field in SF:

This gives a warning that the schema does not match, but this is the only way we could get the job to run without errors. ParentId does not populate, though!

So I tried a more subtle change that seems more appropriate, even if a bit redundant with the relationship mapping, just changing the DB column. This fixes the schema warning, but the stack dump returns. 😞

Finally, I tried changing the name of the field before it makes it to the tSalesforceOutput component. I'm not really happy with this, because at that point in the pipeline, it is not a ParentId. It doesn't really matter, though, because I still get the stack dump upon execution.

So... I could use any assistance in properly defining this relationship. It seems to me that the first version is how it is supposed to work, but really none of them have actually worked! Since our implementation is brand new, this is the newly released 7.3.1 version of Talend Studio.

[Joel2 · Accepted solution]

We were able to figure this out after a few days.

For custom fields, the relationship name is the name of the field with __c replaced by __r, which is pretty well documented.

It is not easy to find for built-in fields, though. The relationship name is the name of the field with "Id" removed. In my case, the relationship name for "ParentId" was simply "Parent".

[JavierP2]

Hi @Joel Kraft​ 

long time ago but i'm having the same issue as you had

I do need to do a look up the field Cliente_c into the field N_de_Cliente__c from the "Account" object, i do have this config but i have the same error as you, any suggstions?

tSalesforceOutput_3 :com.sforce.ws.SoapFaultException: Unexpected element {urn:sobject.partner.soap.sforce.com}type during simple type deserialization

any ideas?

thanks a lot.

 

[JavierP2]

ok, i have fixed, https://trailhead.salesforce.com/fr/trailblazer-community/feed/0D54S00000A8gmZSAR with this post.