Salesforce tInputComponent divided to smaller parts

Mr_Pancake — Fri, 15 Nov 2024 21:33:41 GMT

Hello All,

in my situation I have quite unstable network connection, because of that there appears

java.io.IOException: com.sforce.ws.ConnectionException: Failed to send request to https ...

Unfortunately I need to load all Opportunities from Sales make some transformations and then update them again in Salesforce. In case of having around 300 000 records and this http error I cannot update more than 10% of this data.

To somehow omit the problem with internet I have tried to make a SOQL query with offset in tComponent, like:

"SELECT Id, Name, Amount, CloseDate,

FROM Opportunity

LIMIT 50000

OFFSET 13000"

but there was an error with:

Exception in component tSalesforceInput_2 (opportunities)

java.io.IOException: [UnexpectedErrorFault [ApiFault exceptionCode='NUMBER_OUTSIDE_VALID_RANGE'

exceptionMessage='Maximum SOQL offset allowed for apiName Opportunity is 2000'

extendedErrorDetails='{[0]}'

]

at org.talend.components.salesforce.runtime.SalesforceInputReader.start(SalesforceInputReader.java:120)

at org.talend.codegen.flowvariables.runtime.FlowVariablesReader.start(FlowVariablesReader.java:73)

at rsf.opportunities_0_7.opportunities.tSalesforceInput_2Process(opportunities.java:16956)

at rsf.opportunities_0_7.opportunities.runJobInTOS(opportunities.java:52257)

at rsf.opportunities_0_7.opportunities.main(opportunities.java:52095)

Caused by: [UnexpectedErrorFault [ApiFault exceptionCode='NUMBER_OUTSIDE_VALID_RANGE'

exceptionMessage='Maximum SOQL offset allowed for apiName Opportunity is 2000'

extendedErrorDetails='{[0]}'

]

at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)

at java.base/java.lang.Class.newInstance(Class.java:584)

at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:721)

at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:593)

at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:250)

at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:224)

at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:163)

at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:108)

at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1196)

at org.talend.components.salesforce.runtime.SalesforceInputReader.executeSalesforceQuery(SalesforceInputReader.java:165)

at org.talend.components.salesforce.runtime.SalesforceInputReader.start(SalesforceInputReader.java:107)

... 4 more

which is probably error from Salesforce side, but anyway is there a way, to divide this job into for example 6 smaller (similar) jobs in which every will handle another range of data from Salesforce (for example 0 - 50 000 for job1, 50 000 - 100 000 for job2... ) or any other way to update every record on opportunity (at longer time) but with a possibility to defining a range of data in opportunities for which the whole job should be executed? (to systematically update Opportunities even if a http error occurs along the way?)

Screenshot which showing the design is attached.

Re: Salesforce tInputComponent divided to smaller parts

Anonymous — Fri, 21 Jul 2023 04:31:11 GMT

From the error, we can see that it is a API limitation, Maximum SOQL offset allowed for apiName Opportunity is 2000, the component can return only 2000 records one time using SOQL, you can use a tLoop component to do the loop.

Another options is to read all records from salesforce and split records into multiple files, there is an option on tFileOutputDelimited can split the files automatically.

Then, use a tFileList to iterate each file and update them into salesforce one by one.

Regards

Shong

Re: Salesforce tInputComponent divided to smaller parts

Mr_Pancake — Fri, 21 Jul 2023 10:01:43 GMT

Understood. Thank you very much!

topic Re: Salesforce tInputComponent divided to smaller parts in Talend Studio

Salesforce tInputComponent divided to smaller parts

Re: Salesforce tInputComponent divided to smaller parts

Re: Salesforce tInputComponent divided to smaller parts