Retrieve a Salesforce Id on insert for subsequent inserts

Six Stars

Retrieve a Salesforce Id on insert for subsequent inserts

Hi,

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.

 

Thanks!


Accepted Solutions
Forteen Stars TRF
Forteen Stars

Re: Retrieve a Salesforce Id on insert for subsequent inserts

@paburton96, regarding your 1rst question, thank's to mark this case as solved

TRF

All Replies
Thirteen Stars

Re: Retrieve a Salesforce Id on insert for subsequent inserts

@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.

Manohar B
Employee

Re: Retrieve a Salesforce Id on insert for subsequent inserts

Hi,

 

     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?

 

Warm Regards,

 

Nikhil Thampi

Forteen Stars TRF
Forteen Stars

Re: Retrieve a Salesforce Id on insert for subsequent inserts

To retrieve Salesforce Ids after insert, untick "Extended Output" option and tick "Retrieve inserted ID" option from Advanced settings.
Not necessary to read the object content.

TRF
Six Stars

Re: Retrieve a Salesforce Id on insert for subsequent inserts

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?

 

Thanks!
Alan

Forteen Stars TRF
Forteen Stars

Re: Retrieve a Salesforce Id on insert for subsequent inserts

There is a lot of ways for that. A common pattern is to push the Ids and an other field shared by your database and your Salesforce org to a tHashOutput.
Then in an other subjob, you can get these values using an associated tHashInput to update your database.
The details depend on your own use case, but this is the idea.

TRF
Forteen Stars TRF
Forteen Stars

Re: Retrieve a Salesforce Id on insert for subsequent inserts

@paburton96, regarding your 1rst question, thank's to mark this case as solved

TRF
Six Stars

Re: Retrieve a Salesforce Id on insert for subsequent inserts

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.

 

Process:

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.