Salesforce --> data warehouse sync job;help following tutorial example

One Star

Salesforce --> data warehouse sync job;help following tutorial example

I'm trying to build a sync job that watches for changes in a Salesforce table using the tSalesforceGetUpdated component. I'm trying to follow the example at the very end of this tutorial http://www.youtube.com/watch?v=sx04uHFRjdE&hd=1&autoplay=1&buffer=10&autostart=true&fullscreen=true&..., however, not much time is given to the construction of this sample. I have a couple challenges:
What are the components used to store the server timestamp? In the video, they're called "Initial Server Time" and "Save current Server Time". given that the "Start Date" of the tSalesforceGetUpdated component is spec'd as:
TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",context.LAST_UPDATE)
...it looks like the output of the tSalesforceGetServerTimeStamp is pushed into a context variable. I can't even find the components in the default component list, and haven't been able to work out how you write to a context variable from a component.
Also, there's a "loop" component between the tInfiniteLoop and the tSalesforceGetUpdated components, but the icon is completely different from the one in my component list.
Big picture, the goal is:
* Run an infinite loop
* At each interval, poll Salesforce for all records changed since the last iteration
* Test retrieved records against existing records in the warehouse DB
* Write changed records as updates; new recrds as inserts.
* Repeat
Any help working this out would be greatly appreciated! Thanks in advance. -Ben
EDIT: based on this http://talendtutorials.wordpress.com/2012/01/13/storing-temporary-values/ it looks like two of the components - the ones receiving the timestamp - are "tJavaRow" components. Still at a loss for the last one, identified as "loop" (since the icon is different from that in my v5.3 TOS). -Ben
EDIT: and yet, I don't seem to have a tJavaRow component in my TOS <sigh>. Struggling onwards... -Ben
EDIT: ok, hopefully final edit for the day: I found out what the last component is: it's a "tJava" component. And, since there's zero information on the configuration or code for this component in the example in the tutorial, that tutorial is kind of useless. And a tease. I'd really like to get this to work, so if anyone from Talend is listening, could you please provide some additional information about the "salesforce_dw_sync" job in the tutorial I cite at the top of this post? Specifically:
What is the Java code for each of the tJava and tJavaRow components?
What are the other configuration parameters for the various tSalesforce* components?
Lastly, I can't find the tJava nor tJavaRow components in my TOS-DI v5.3.1 installation. I tried with the Big Data TOS as well, which the documentation says includes these and the other components on the "Custom Code Palette". However, that palette is missing entirely my from install. I'm just missing something fundamental, methinks.
Thank you very much. -Ben
Moderator

Re: Salesforce --> data warehouse sync job;help following tutorial example

Hi,
I have make a testing on talend open studio for Data Integration 5.3.1r104014, there are tjava and tjavarow component (see my pic).
The components might have been hidden for some reason or the components are loaded, you can active them by following the steps:
1. Open the project settings window, click Designer, then Palette Settings.
2. Check if the components are in the Hide side, if so, move them to the Show side. If the components have been in the Show side, but you do not sill find them in the palette, move them to the Hide side, and move them back to Show side.
In addition, there is a "code" tab in job desktop next to "Designer" tab. If you use tjava or tjavarow, the java code will be generated automatically by talend studio.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Salesforce --> data warehouse sync job;help following tutorial example

Hi Sabrina,
Thank you so much! I knew I was missing something pretty straightforward. I now have access to the "Custom Code" components, including both the tJava and tJavaRow components.
I think I can figure out how to use the tJavaRow component in the manner suggested by the tutorial.
The last thing I'm missing is the configuration and/or code for the tJava component identified as "loop" in the aforementioned tutorial. Is there any way for you to help me with that?
Thanks again, and have a great day!
-Ben
One Star

Re: Salesforce --> data warehouse sync job;help following tutorial example

Hello Ben,
Were you able to create this sync job?
If so, could you please share the code / configuration used?
Thanks,
Rafael
One Star

Re: Salesforce --> data warehouse sync job;help following tutorial example

Hi Rafael. I did not ever get this up and working properly, and - sadly - I've been pulled off onto other tasks. If I ever do make progress, I'll update this thread. Good luck! -Ben
One Star

Re: Salesforce --> data warehouse sync job;help following tutorial example

Hi Ben. Did you manage to do this? I'm facing the same challenge now...
Thanks, goppi
One Star

Re: Salesforce --> data warehouse sync job;help following tutorial example

Hi goppi,
Alas, I did not ever get this working, and then my company closed down and I've not been working in Talend for several months. Good luck, and if you figure it out, please come back to this post and enlighten us as to your solution!
Regards, Ben
One Star

Re: Salesforce --> data warehouse sync job;help following tutorial example

Thanks for your quick reply. Would be a shame if this stays unresolved.
One Star

Re: Salesforce --> data warehouse sync job;help following tutorial example

I managed to get it working, but considering it's my first ETL job definition, I'm certain there are better ways.
The process in the video worked with Context variables. I used the Context variables, but I couldn't figure out if they would keep the values after f.i. a restart of the ETL server. As such I'm also writing the value into a SQL table. I also played around with one email notification. I'm here only describing the main components:

tJavaRow_3 - take the previously stored Enddatetime, substract 10 minutes (just in case and overlapping the prev query) and store it in Startdate
context.Startdate = TalendDate.addDate(input_row.value,"yyyy-MM-dd HH:mm:ss", -10, "mm" );

tJavaRow_1 - take the Salesforceservertimestamp and store it in Enddate
context.Enddate = TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", input_row.ServerTimestamp);

tJava_1 - just some noise
System.out.println("Startdate: " + context.Startdate);
System.out.println("Enddate: " + context.Enddate);


tJavaRow_4: another noise
System.out.println("Changed record: " + input_row.name);


tJavaRow_2 - update Start and Enddate and update last updated value in DB:

output_row.ServerTimestamp = context.Enddate;
context.Startdate = TalendDate.addDate(context.Enddate,"yyyy-MM-dd HH:mm:ss", -10, "mm" );
context.Enddate = TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", input_row.ServerTimestamp);

Here is the job:

Hope that helps
Goppi
One Star

Re: Salesforce --> data warehouse sync job;help following tutorial example

Nice! And really fantastic you came back to update this thread - no doubt you've helped out a number of folks with the same issue (or folks who will in the future) - thanks. I hope I'm in a position to explore this again at some point... -Ben