I am importing data from a 3rd party csv file to a Microsoft SQL Server database table that will ultimately be inserted into 3 separate Salesforce tables.
In my Talend job, I am selecting the data for the first table insert via a tMSSqlInput component, running it through a tMap component and inserting it into Salesforce using a tSalesforceOutput component.
I will need the Ids generated from that first table insert for the inserts into the other two Salesforce tables.
Is there any straightforward way of retrieving the Ids (and possibly updating the local database table with them) so I can use them in the subsequent Salesforce inserts?
I am a complete novice at Talend, so as much detail as possible would be greatly appreciated.
Solved! Go to Solution.
@paburton96,from the salesfroce you want to retrieve the Id's means you need read again that object based on some created date or modified data,while inserting data into sales-force you can retrieve from the Out of sales-force object in Talend.
Since you would like to generate the sequence id while during data extraction and then load it back to your local database, the easiest way will be to create a sequence in your local DB. During your query, fetch the next value from this sequence inside DB as an additional parameter.
If you are doing by this method, the overhead to load the sequence id back to your database will be automatically eliminated. But please note that every time you are running the query, the sequence id will be automatically incremented. So any adhoc run of the query will result in break in the ids. Since it is still unique, it should not create any issue in real time situations.
If the reply has helped you, could you please mark the topic as solution provided?
TRF - Thank you very much!
I unchecked "Extended Output" and checked "Retrieve inserted ID" and the Id field shows up now in my tMap component.
Now the question is, how do I get that value into my local DB table?
Sorry if I am a little dense on this matter, but I am making some progress - just got stuck again. Let me add some pics and maybe that will help illustrate my confusion.
1. Receive a file from a 3rd party
2. Stage the file in a MS SQL Server database table - Table A
3. Talend job reads the records from Table A --> Inserts records into Salesforce (which generates the Id value)
4. Update the Table A with the newly created SF Id value
I've included pics of the job steps. In Table A, I have an identity field as the PK. It would make sense that if I could pass that through the process, I could use it for the update at the end to ensure I am matching the correct SF Id to the correct local table record. However, since the identity field in the local table doesn't exist in SF, I'm not sure how to pass that through the tMap so that it is available when I go to update the local table.
I do see the SF Id in the 2nd tMap, I just don't know how to get those values updated in my local table for each row.
If I can provide more info for further clarification, I'd be happy to.