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.
Solved! Go to Solution.
The date format is not valid.
As your date looks to be aligned on GMT, this one should work "YYYY-MM-DDThh:mm:ssZ".
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?
@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
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
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
As expected and explained in my previous post, the date must be converted to a String using TalendDate.formatDate() method.
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.
...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.
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.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema
Pick up some tips and tricks with Context Variables