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

Highlighted
Six Stars

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

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.

 


Accepted Solutions
Six Stars

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

C0.PNGC1.PNGC2.PNGC3.PNGC4.PNG


All Replies
Five Stars

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

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.
Six Stars

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

CaptureCaseJob.PNGAt the bottom, notice the maxDate printout from tLogRow1 has correct date format, but the maxDate2 printout from tJavaRow4 has wrong formatCaptureCase2.PNGCaptureCase3.PNGCaptureCase4.PNGCaptureCase5.PNGCaptureCase6.PNG

Fifteen Stars TRF
Fifteen Stars

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

The date format is not valid.

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


TRF
Six Stars

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

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

Six Stars

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

Capturecase7.PNGCapturecase8.PNGCapturecase9.PNGCapturecase10.PNG

Six Stars

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

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

Thanks

Fifteen Stars TRF
Fifteen Stars

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

@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

 

 


TRF
Six Stars

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

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

 

 

Six Stars

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

Capture10.PNGCapture11.PNGCapture12.PNG

Five Stars

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

Hi , 

 

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

Please find the screenshots : 

1.PNG2.PNG3.PNG4.PNG

 

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

Regards, 

Vatsal

Fifteen Stars TRF
Fifteen Stars

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

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


TRF
Six Stars

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

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

Six Stars

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

C0.PNGC1.PNGC2.PNGC3.PNGC4.PNG

Fifteen Stars TRF
Fifteen Stars

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

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.


TRF
Six Stars

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

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.

 

 

Fifteen Stars TRF
Fifteen Stars

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

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

TRF

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog