September 20, 2008 at 7:18 pm
What's the best approach to synchronize a linked server to a local SQL server database? DBAmp, which is OLE DB provider used for Salesforce, is utilized for setting up the linked server object. My initial thoughts were writing CREATE TABLE, UPDATE, INSERT, & DELETE statements. However, I'm not sure if that's the quickest approach in terms of man hours or implementation.
September 21, 2008 at 6:09 pm
I think what you're essentially talking about is an ETL job.
You might consider a couple of options.
1. Use your OLE DB provider as a datasource in SSIS; use agent to schedule the SSIS job
2. if it's really really simple, you can write a stored procedure to keep the tables up-to-date; use agent to schedule the Stored proc.
Either way you're stuck with /how/ to programatically apply just the changed data (delta), and there are a number of good ETL/Data Warehousing tricks for capturing delta, if you're really lucky, there will be 'lastupdated' columns in your datasource you can leverage... otherwise it gets trickier, but is still very possible. Google will be very helpful and this article is a good introduction to the topic http://en.wikipedia.org/wiki/Change_data_capture
I think there is also a SSC.com forum dedicated to ETL or Data Warehousing, that might be another good resource for you to leverage.
Good luck!
~Craig
Craig Outcalt
MCITP, MCDBA
Craig Outcalt
September 22, 2008 at 12:59 am
First hit on Google for "DBAmp oledb" brings up this link: http://www.salesforce.com/web-common/assets/doccache/MultiForceDir/01530000000Ev3IAAS.pdf
Looks like exactly what you want AFAIK - check the section on SP_Refresh and SP_RefreshAll.
Regards,
Jacob
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply