Software Architecture – Sync Data between offline/Disconnected databases – SSIS – BIDS

Problem definition:
ERP Product is installed in head office and 5 branch offices such that each location installation has its on database. Locations are not connected via Internet that is they are acting as diconnected databases.

We want to sync databases of different branches.

Solution:
Some possible solutions to problem are as:
• Replication
• ETL
• Sync Providors
• Sync Framework
• Change Data Capture
We will compare and analyze pros and corns of these method in separate post. But here we will use ETL approach to solve this problem.
At closing of each working day, data is fetched from each branch database and new data is posted to other branches and head office database Server via email. So each database read email and executes the database differences from other databases.
So we can break whole process in 4 programming Steps.
1. Extract data from source database and convert them into a text file with data/time stamp attached.
[Implemented as SSIS Package and run via C# Code]

For details see post here

2. Read files from a folder in step 1, and send them as attachment in email via Outlook.
[Implemented by interop of MS Outlook API]

For details see post here

3. On other side, emails are pulled automatically via outlook and attachments are downloaded and saved in a local folder in hard disk.
[Implemented by interop of MS Outlook API]
For details see post here

4. Text files s are read from folder and loaded in to database.
[Implemented as SSIS Package and run via C# Code]

For details see post here

Future consideration:

1. How to identify the differential of data between the daily move of data.
2. This solution is working for one table how to handle more tables for data sync process.
3. There are many hardcoded parameters that needs to be fetched from configuration files.

Source: download from here Rename the file to ssis-solution-toupload.zip to extract.
Feedback is welocme

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: