2 Ways Synchronization - Salesforce & MSSQL

One Star

2 Ways Synchronization - Salesforce & MSSQL

I would like to know if the following scenario is possible using TOS Data Integration.
If yes, how to do it in TOS.
As well is there any way in Talend to compare the LastModified in Salesforce before the update?
Steps to Update Salesforce
1. Look at Pivotal (Using MSSQL) for records updated in last, say, hour
2. Gather matching records from salesforce (i.e. those with same Pivotal Ids from list above)
3. Is there actual change in the fields we care about? (i.e. compare actual values of key fields...as the timestamp change may have happened based on something we don't care about)
4. If so, is change from Pivotal newer (or is the Account record from Pivotal newer)?
5. If so, use it and update Salesforce.com record (also update a new Account 'Updated From Pivotal On' field on Account with Pivotal's timestamp)
Steps to Update Pivotal
1. Look at Salesforce for records updated in last, say, hour (can even excluse those with an 'Updated from Pivotal On' field that have a timestamp from the last hour so we can avoid what was just updated)
2. Gather matching records from Pivotal (i.e. those with Pivotal Ids from list above)
3. Is there actual change in the fields we care about? (i.e. compare actual values of key fields...as the timestamp change may have happened based on something we don't care about)
4. If so, is change from Salesforce newer (or is the Account record from Salesforce newer)?
5. If so, use it and update Pivotal record (also update a new Account 'Updated From Pivotal On' field on Account with Pivotal's timestamp)
Any guidance is greatly appreciated.
One Star

Re: 2 Ways Synchronization - Salesforce & MSSQL

Hi
Steps to Update Salesforce: Every time after finishing update, you'd better use tMSSQLLastInsertedId to get and store last id into a file. When next time you want to update, get this id and do update from this record.
Or you might use tMSSQLSCD to trace the records.
Steps to Update Pivotal: You might use tSalesforceGetUpdate.
Regards,
Pedro
One Star

Re: 2 Ways Synchronization - Salesforce & MSSQL

1. Is this the correct Step To Update Salesforce FROM MSSql/PIVOTAL:
tInfiniteLoop --Iterate-->tMSSQLInput --> tMSSqlSCD-->TMap-->tSalesforceOutput

2. Is this the correct Step To Update MSSQL (Pivotal) FROM Salesforce:
tInfiniteLoop --Iterate-->tSalesforceGetUpdate-->TMap-->tMSSQLOutput

3. tSalesforceGetUpdate only allow 30 days date range, so if we use this component, do we have to change once a month then, is it?
4. In the Scenario Tracking Changes Using SCD on the Talend Component Guide, there is a SCDTable I am not sure for my scenario where to put into the job diagram? Is it as a MSSQL table --> tMSSQLInput as in #1?
5. Because we want to update based on certain field of the db records in MSSQL & Salesforce, I think do we have to use SCD feature?
Looking forward your input, thank you for your help till now, appreciate it.