SalesForce Data Integration with lookup relationships
Hi,
I've been trying to get this working and I can't figure out for the life of me why I keep getting this error. Maybe someone has already run into this and can explain what I'm doing wrong. Actually I believe it has to do with the tSalesforceOutput_1 Advanced Settings where I'm setting the Relationship Lookup to use External IDs. Here's what I have in my job and what I've done:
In our Salesforce Org, 2 of the custom objects are Distribution_Center__c and Country__c. The Country__c object has a lookup reference to a Distribution_Center__c and the field is called exactly Distribution_Center__c. Same as the actual object.
What I needed to do is to move the records for these 2 objects from Production to a Sandbox. Based on some tutorials online I went into the Sandbox objects and created External ID fields for the two objects, both called Sandbox_Migration_Id.
Then, in the Talend Job I added first a copy of the Distribution Center object from tSalesforceInput (Production Distribution_Center__c object) --> tMap --> tSalesforceOutput (Sandbox Distribution_Center__c object) making sure that the tMap is moving the Id from Production into the Sandbox_Migration_Id field in the Sandbox.
I used UPSERT so I can keep running this whenever I need , of course using the Sandbox_Migration_Id as a key.
Then I added a second part to the job with tSalesforceInput (Production Country__c object) --> tMap --> tSalesforceOutput (Sandbox Country__c object) with the same tMap moving the Id from Production into the Sandbox_Migration_Id field in the Sandbox.
Here I understood that in order to make sure that the Distribution_Center__c field of the Country__c object is properly linked to the correct Sandbox Distribution_Center_c object I need to use the Advanced Settings for the tSalesforceOutput (Sandbox Country__c object) to add that relationship. Now the problem is that in the tutorial I followed, and even on Talend documentation website, the example was most likely using an old version of Talend which was only requiring 4 columns to be filled in. Now in the newest version 6.3 that table requires 5 columns and I'm not sure I'm filling the 5th one correctly.
Here are the 5 columns and what I added in each:
Column name of Talend schema: Distribution_Center__c (selected from the dropdown list).
Field name: "Distribution_Center__r"
Lookup field name: "Distribution_Center__c" -
This is the one I'm not sure about. Module name: "Distribution_Center__c"
External id name: "Sandbox_Migration_Id__c"
Can anyone find where and what I'm doing wrong? Thank you in advance!
Error tSalesforceOutput_2:
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:397) at org.talend.components.salesforce.runtime.SalesforceWriter.logout(SalesforceWriter.java:536) at org.talend.components.salesforce.runtime.SalesforceWriter.close(SalesforceWriter.java:527) at org.talend.components.salesforce.runtime.SalesforceWriter.close(SalesforceWriter.java:54) at prod2sb.prod2cnsb_0_1.Prod2CNSB.tSalesforceInput_1Process(Prod2CNSB.java:2778) at prod2sb.prod2cnsb_0_1.Prod2CNSB.tSalesforceInput_2Process(Prod2CNSB.java:1466) at prod2sb.prod2cnsb_0_1.Prod2CNSB.runJobInTOS(Prod2CNSB.java:3121) at prod2sb.prod2cnsb_0_1.Prod2CNSB.main(Prod2CNSB.java:2978) Caused by: com.sforce.ws.SoapFaultException: Unexpected element {urn:sobject.partner.soap.sforce.com}type during simple type deserialization at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:212) disconnected at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156) at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99) at com.sforce.soap.partner.PartnerConnection.upsert(PartnerConnection.java:1150) at org.talend.components.salesforce.runtime.SalesforceWriter.doUpsert(SalesforceWriter.java:383)
Based on your description, I am assuming you are using Talend 6.2.1. Please change your settings like this and let us know how it works out:
Column name of Talend schema: Distribution_Center__c (selected from the dropdown list).
Field name: NULL
Lookup field name: "Distribution_Center__r"
Module name: "Distribution_Center__c"
External id name: "Sandbox_Migration_Id__c"
Please note that this will only work if you unselected "Ignore NULL fields values". If this is set, you will most likely have to set "Field name" to "Distribution_Center__c".
Hi,
in TOS_BD 6.3.0 tSalesforceOutput actually has 5(!) fields:
Column Name of Talend Schema
Lookup field name
Lookup relationship field name
Module name
External id name
Honestly I couldn't find out what "Lookup field name" is good for, and inmy experiments it actually isn't even used.
The following values worked for me - note that Lookup field name for Case is a string without any meaning:
For a custom object (Line_Item_c which belongs to Invoice__c):
Invoice__c, "Invoice__c", "Invoice__r", "Invoice__c", "ExtID__c"
For a case associated to both an account and a contact:
AccountId, "Accountx", "Account", "Account", "ExtID__c"
ContactID, "Contactx", "Contact", "Contact", "ExtID__c"
Talend Open Studio for Big Data Components 6.2.2 - Reference Guide (EN) says on the Lookup field name parameter: "
Lookup field name: the name of the lookup field. It refers to the lookup field of the module specified in the
Module Name field in the
Basic settings view. This column needs to be specified when there are NULL input values for it and the
Ignore Null check box is cleared."
I have problems to understand this, and unfortunately the example screenshots in the guide still show the old (4 value) parameters.
However some testing resulted in the following working example (even if null is passed as a contact):
AccountId, "AccountId", "Account", "Account", "ExtID__c"
ContactID, "ContactId", "Contact", "Contact", "ExtID__c"
Note that AccountId and Contactid are the field names as you find them in the workbench or the API documentation.
