topic Re: Update a MS SQL Server table with auto-gen'd Salesforce Id in Talend Studio

Update a MS SQL Server table with auto-gen'd Salesforce Id

Anonymous — Sat, 16 Nov 2024 07:44:07 GMT

Hi - I am a Talend novice and am trying to upload records to Salesforce, get the auto-generated Id and update the local database with that Id for each record that was selected/uploaded. I have outlined my process as follows:

1. Receive a file from a 3rd party

2. Stage the file in a MS SQL Server database table (Table A)

3. Read the records from Table A and insert the data into Salesforce (which auto-generates an Id value)

4. Update Table A with the newly created SF Id value

I cannot figure out how to get Talend to make the update in Table A. Pics of my job are attached.

If I can provide further clarification, please let me know and thanks in advance for your help.

Thanks!

Re: Update a MS SQL Server table with auto-gen'd Salesforce Id

fdenis — Mon, 03 Sep 2018 10:12:17 GMT

I think that your recorkey is a unique id of your db table.
in output you did not need field option. you just have to set recordkey as key.

Re: Update a MS SQL Server table with auto-gen'd Salesforce Id

Anonymous — Mon, 03 Sep 2018 19:36:54 GMT

Thank you for your response. I'm not sure I understand what you are saying.

RecordKey is the PK in my local table.

SalesforceId is the PK in the table I am inserting into in Salesforce.

I am passing the RecordKey value through the process so that when I get the auto-generated SalesforceId back after insert into Salesforce, I can update the local table with the corresponding SalesforceId for that row. RecordKey is what lets me map the new SalesforceId to corresponding row locally.

For example, if I have 1 record in my local table with RecordKey value = 1

I insert the value from that row into Salesforce and an Id of "ABC" is generated by Salesforce

I need to update the SalesforceId value in my local database table for the row where RecordKey=1 with the value "ABC"

Re: Update a MS SQL Server table with auto-gen'd Salesforce Id

fdenis — Tue, 04 Sep 2018 08:44:04 GMT

we are inline.

in tOutputXxx uncheck "field option".
check key for Recordkey. (so Talend is going to use it for update ….. where Recordkey=xxx)

Re: Update a MS SQL Server table with auto-gen'd Salesforce Id

Anonymous — Tue, 04 Sep 2018 17:33:14 GMT

I appreciate your taking the time to help, but please keep in mind I have never done this before and have very minimal experience with Talend.

I unchecked the "Use Field Options" box on the Advanced settings tab of the tMSSqlOutput_1 component as you mentioned in your post.

You also mentioned "check key for Recordkey." Where? In the tMap_2? The tMap_2 component already shows the Recordkey as the PK.

If you could maybe provide a pic or perhaps a detailed explanation on where and how I am supposed to "check key for Recordkey", that would be very helpful.

Thanks!

Re: Update a MS SQL Server table with auto-gen'd Salesforce Id

Anonymous — Tue, 04 Sep 2018 18:12:28 GMT

Actually, I think I got it. I did the insert and got it to update the newly generated SF Id into the local table.

Thank you so much for your help and patience!!