Talend Open Studio:Version: 6.3.1
Salesforce Soap Version 37
Last year, we had set up some Data Integration with our CRM and on an certain Job it came an Data Collision from Time2Time with Account-ID from similar Prospects and Customer Record.
The Job does Upsert our Customer Quotes from our ERP ( Infor) to Salesforce.
The unique Key is the "Quote Number".
The Innerjoin Lookup is the Account-Number from the ERP System for referencing the Account-ID in Infor.
Account-Number ( ERP):
We have some Customers and some and some Prospects. The Customers are having the Number with 5 Digits and the Prospects have the same one plus an CHAR (K) at the beginning. For Example: K12345 ( Prospect) and 12345 (Customer). The Customer get an new Record and isn't converted in any way from an Prospect.
And i think here is the pain point.
The Error Mail ( some custom Text):
There is a problem.
Mon May 22 17:34:07 CEST 2017
PROD_17_11 (Default) / tSalesforceOutput_1 Java Exception : java.io.IOException:Attempting to update (as part of an upsert) parent field Account__c with new value 0012400001BRR9VAAX, current value is 0012400000kzBuBAAU
So the id's are referencing to an an similar Account but not identic one. Here are an short Overview of the 2 Accounts in SF which cause an Crash:
Accounts-View in Salesforce which are related to the 2 ID's:
The Data from the Records are the same Company but have different ( every green marked text is identic) Accouint-Numbers ( Infor FirmenNr.) as youi can see on the Picture.
The Unique ID stored in the Oracle Database are also not Identic:
IK ( Unique ID):
The Problem occours not really often but from Time2Time and alway with similar Accounts from an Prospect and an Customer
And here is my Job:
Whole Overview Job:
I had make an new Job for testing based on this one but only with an Account Lookup from the certain Account's in Salesforce.
Then comes an error that i have an duplicate entry and shows the External-ID ( Quote-Number) but i havent any second Quote Number in my SF-Instance.
I mad an another test with the same Job with the 2 Account ID's on an Sandbox an here Talend says the same that an Duplicate Exist. But this is still less the Truth, cause on the contrary to the prod Instance there is no Record with the Quote-Number in the System which is so-called as an Duplicate.
So i think Talend mixed up sth. in the Job itself.
best regards john
Ok it seems, that i have to solve my Problem in any way cause the following Tasks are built up on this one.
So any ideas?
@ArvinRapt may you have an clue or an idea for me ?
Thanks in advance
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema
Pick up some tips and tricks with Context Variables