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

[elkhounds]

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.

[vatsal_ras]

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.

[elkhounds]

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

[TRF]

The date format is not valid.

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

[elkhounds]

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

[elkhounds]

[elkhounds]

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

Thanks

[TRF]

@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

[elkhounds]

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

[elkhounds]