A Data Merge Problem
Here’s an interesting problem for you. A fellow asked the following question on a technical forum I follow, “How do I merge data from a remote source into my database?” The remote source provides data through an online API. He maintains a local database for those periods of time when the application is used without internet access. So, he wants to poll the online source when connected, and merge the data into his local data store.
There are lots of tools for doing this kind of work. They have been developed for many years, because we are constantly having to solve this problem in many forms. Some thought of using replication or mirroring; but that wasn’t possible because the remote data is not in an ODBC format.
Would it make sense to write an ODBC wrapper around the remote data so you could use more traditional tools for merging data?
Another approach mentioned was to bring all the data into a temporary database or table, and then merge the data using standard merge techniques.
SSIS is an obvious possibility because he is writing his application in Dot Net. However, in his situation, he is using the compact edition of SQL Server, and disk space is a premium commodity. However, you could probably write a service that would do the merge work for you on a back end, and simply forward the changes to the mobile version of the application. More work…but it solves the small footprint problem.
What would you do? Wny not leave some of your ideas here online, or drop me an email with your solution to btaylor@sswug.org.
Cheers,
Ben