locked
Synchronization between heterogeneous data sources RRS feed

  • Question

  • Hi,

    I have a complicated scenario and I have some issues with it. I'd like to synchronize data between different data sources and I don't know how can i do it. First, there is an "external" Excel document. It contains a denormalized table with lots of data. I want to synchronize Excel data to an Entity Framework model based database on the client. When it is completed, users will repair incorrect data using an own WPF application. (Additionally, if they want, they can add new data) When it is also completed, I will sychronize the client data to the server database (offline scenario).

    So, step by step:

    1. Microsoft Excel document (denormalized data)
    => /unidirectional/
    2. SQL Server 2008 DB (EF) on the client
    <=> /bidirectional/
    3. SQL Server 2008 DB (EFl) on the server

    In addition, there is also a new issue. There are no unique identity keys in Excel documents. I can only use company names as unique identity keys. For example:

    OrganizationName: Microsoft
    City: Redmond
    Boss: Bill Gates
    Gender: Male
    + 200 data :)

    =>

    1 Organization object (key: OrganizationName)
    1 Settlement object (key: City name)
    1 Person object (synchronized with the organization object because there are no identity values and a person name is not enough)
    + 10 entity objects :)

    I hope you will can help me. Practically, I need some ideas. Thx.
    • Edited by János Janka Tuesday, May 19, 2009 3:56 PM
    • Moved by Max Wang_1983 Thursday, April 21, 2011 1:31 AM forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
    Monday, May 18, 2009 8:32 PM

All replies

  • From the big picture
    1. Transfer data from Excel to SQL server 2008, can you check out SQL Server Integration Services, http://msdn.microsoft.com/en-us/library/ms141026.aspx and see if you like it?
    2. Sync data between SQL server databases, Sync Services for ADO.net can be a good choise  Please see http://msdn.microsoft.com/en-us/library/ms141026.aspx.

    BTW, you had better define a PK on the relational database.  You can consider using the Company Name as PK but you need to make sure it is unique in the corresponding data table.


    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, June 4, 2009 9:48 PM
    Answerer
  • Thanks. So, I have to install a SQL Server Standard/Professional/Enterprise Edition to my home PC to able to use SQL Server Integration Services. But I'd like to use these services on every user's PC without installing SQL IS. Possible it? (Sorry, I haven't read how does it works) Or I have to use Sync Services? I've realized SyncFX CTP2 supports data transformations and there is a SqlSyncProvider too. So, I can use a FullEnumerationSyncProvider or some similar provider to synchronize Excel data to my SQL Server database using data transformation services. Or it is not the best way?

    It is no a secret that we would like to make an online partner service that everyone can use worldwide. (Here is the old site: Partnerinfo.EU) It will be an online business service to find partners (organizations) with their HD videos. Of course, we make videos with organizations, such as Microsoft Hungary Kft. ;) Therefore I want to synchronize organization data to a common database on the server. Every organization can use the common organization database (like a MS identity service, such as Live Personal Card). But I need to support the offline scenario too. Therefore there is a client fx and there is a WPF application to manage it. Moreover, we get data from a third party (excel data) and I have to sycnhronize it to our server database too. So, organization keys have to be unique worldwide. Ooops, but the key is an auto incremented GUID now => SyncFx won't be able to compare data correctly. I have to find some global key values.

    Client1 <----
                      \
    Client3 <-----> Server <----  Excel
                      /
    Client2 <----

    It is not a simple case. But thanks for all your help.
    Wednesday, June 10, 2009 5:25 PM