topic Re: Incremental load from Salesforce table using max(createddate) from local DW in Talend Studio

Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Thu, 25 Apr 2019 23:16:59 GMT

I need to create a job that performs an incremental load from SF table to our local table in Oracle. The maximum date of the last row in the local table is to used to select only rows from the SF table that are greater than the maximum date.

In Talend, I have created the following components: a tOracleInput to get the max date from the local table and convert to the "yyyy-MM-dd'T'HH:mm:ss" date format , tLogRow that print the date value and a tJavaRow that includes the globalMap.put("maxDate", input_row.maxDate) and a System.out.println of the date value.

All 3 component's schemas have the maxDate context variable (both input and output), Date for the DB type and the "yyyy-MM-dd'T'HH:mm:ss" format for the data pattern. This format was selected because SF apparently requires querying against date columns with time to be in the "yyyy-MM-dd'T'HH:mm:ss" format.

When I run the job, the tLogRow outputs the date correctly in the desired date format, but the tJavaRow outputs the date in a different date format that cannot be used against the SF table. Any suggestions would be greatly appreciated.

Re: Incremental load from Salesforce table using max(createddate) from local DW

vatsal_ras — Fri, 26 Apr 2019 16:19:01 GMT

Can you please share the screenshot of the jobs. It will help to understand where you are losing the date format. Also use String as data type. You can also achieve the desired by saving max date as context and later using in salesforce query.

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Fri, 26 Apr 2019 18:06:19 GMT

At the bottom, notice the maxDate printout from tLogRow1 has correct date format, but the maxDate2 printout from tJavaRow4 has wrong format

Re: Incremental load from Salesforce table using max(createddate) from local DW

TRF — Fri, 26 Apr 2019 21:43:10 GMT

The date format is not valid.

As your date looks to be aligned on GMT, this one should work "YYYY-MM-DDThh:mm:ssZ".

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Fri, 26 Apr 2019 23:38:08 GMT

Thanks for offering help, TRF.

I made the date format change suggested, but the outcome is the same. See new snap shots. They show the schemas for each component with the date format suggested.

Can you see anything else that may be the source of the problem?

Thanks

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Fri, 26 Apr 2019 23:39:34 GMT

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Fri, 26 Apr 2019 23:41:47 GMT

Vatsal_ras, have you had a chance to review the screenshots of the jobs?

Thanks

Re: Incremental load from Salesforce table using max(createddate) from local DW

TRF — Sat, 27 Apr 2019 09:32:04 GMT

@elkhounds, you didn't share your tSalesforce component with the condition.

Anyway, for the example here is what I do1

1- populate a String global variable with the expected value (here, the current date) and the format you have to use for Salesforce SOQL queries:

globalMap.put("timestamp", TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ssZ", TalendDate.getCurrentDate()));

2- reuse the global variable for the SOQL condition:

"LastModifiedDate < " + (String)globalMap.get("timestamp")

3- adjust according to your use case

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Mon, 29 Apr 2019 02:44:01 GMT

Hi TRF,

Implemented your suggestions, but the outcome is the same. The maxDate value is the maximum date from my local table (sf_casehistory). Here is the query in the GetMaxCaseHistoryDate component: ""select max(cast(createddate as timestamp)) from sf_casehistory"" and the cast as timestamp function in Oracle converts the createddate to a timestamp with the "YYYY-MM-DD'T'HH:mm:ss'Z'" date format. This format is required to query the createddate from the SF table (casehistory). Here is the query in the CaseHistory component: "SELECT CASEID,NEWVALUE,OLDVALUE
FROM CASEHISTORY WHERE createddate > " +(String)globalMap.get("maxDate") +"".

The GetMaxCaseHistoryDate component connects to the tLogRow_1 component and passes the maxDate column. The tLogRow_1 connects to the tJavaRow_4 component (all 3 component's DB type is DATE and the Date format is "YYYY-MM-DD'T'HH:mm:ss'Z'"). tJavaRow_4 connects to the CaseHistory component.

When Running the job, the tLogRow_1 correctly outputs the maxDate as 2018-10-282T15:20:09Z - the correct date format. However, the tJavaRow_4 incorrectly outputs the maxDate as Tue Oct 09 15:20:09 PDT 2018 - the incorrect date format.

Please refer to the screen shots for more information

Thanks

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Mon, 29 Apr 2019 02:47:37 GMT

Re: Incremental load from Salesforce table using max(createddate) from local DW

vatsal_ras — Mon, 29 Apr 2019 10:58:53 GMT

Hi ,

I was able to solve this issue by using a tMap in between and converting the date into String.

Please find the screenshots :

Please mark this as Solution and provide kudos if it helps you

Regards,

Vatsal

Re: Incremental load from Salesforce table using max(createddate) from local DW

TRF — Mon, 29 Apr 2019 11:30:24 GMT

As expected and explained in my previous post, the date must be converted to a String using TalendDate.formatDate() method.

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Mon, 29 Apr 2019 21:32:43 GMT

Hi Vatsal,

Thank you very much for your support.

Now that the issue has been resolved (by using a tMap in between and converting the date into String),

how do I formulate the query to select all rows from the SF casehistory table that are > than the maxDate variable? Please see the screenshots of the components.

Ray

C0.PNG
C1.PNG
C2.PNG
C3.PNG
C4.PNG

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Mon, 29 Apr 2019 21:35:15 GMT

Re: Incremental load from Salesforce table using max(createddate) from local DW

TRF — Mon, 29 Apr 2019 21:51:12 GMT

Should be
...where CreatedDate > " + (String) globalMap.get("yourVariable")

"yourVariable" must be formated like this "YYYY-MM-DDThh:mm:ssZ"
Also you should respect the upper/lower case for all fields in both the query and the schema.

Edit. fields on Talend side must match fields API name on salesforce side.

Re: Incremental load from Salesforce table using max(createddate) from local DW

elkhounds — Tue, 30 Apr 2019 22:18:19 GMT

I finally resolved this issue by using the following date format: "yyyy-MM-dd'T'HH:mm:ss'.000Z'". Notice the '.' before '000Z'. No other date formats worked against the createddate column in the SF table.

Thanks Vatsal and TRF for your help.

Re: Incremental load from Salesforce table using max(createddate) from local DW

TRF — Tue, 30 Apr 2019 22:35:40 GMT

Strange, I've tried with the format I gave to you and it works fine