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]

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

[TRF]

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

[elkhounds]

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

[elkhounds · Accepted solution]

[TRF]

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.

[elkhounds]

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.

[TRF]

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