An order management system (OMS) offered by our client of vteam #450 provided an inventory module to its users. This OMS is a web based management system providing direct response, eCommerce and multi-channel retail industries with scalable Order Management and Call Center solutions that optimize sales performance. It is developed using ASP.Net WebForms, Vb.net and SQL Server. Our client now required 3rd Party Data Synchronization for its inventory module.
The purpose of this project was to create a series of data extracts (Exports) that can be performed at regular intervals to synchronize a third party system. The intent is that this OMS system will be the “master” of the data. All adds, edits, deletes will occur in the OMS system. Those changes will be passed along to the third party system via the regularly scheduled data extracts.
The SQL server provides a feature called Change Tracking, which has the ability to track changes to individual tables. When enabled for a table, a set of special SQL commands can be run to identify records (via primary key) that have been inserted, updated or deleted.
Implementation:
Data Synchronization was accomplished using the MSSQL Server Change Tracking Feature. Store procedures were developed for inserted, modified or deleted rows in the database. To track the row change version we created a synchronization control table. The SQL server process maintains a big integer variable called SYS_CHANGE_VERSION that increments for every changed row. This integer variable stored in the synchronization control table allows query only effective changes since the last time the process was run.
Once the file is output to CSV successfully, we updated the last changed version value, for the entity record in the control table, to contain the value extracted from CHANGE_TRACKING_CURRENT_VERSION() function when the query was run.
Conclusion:
The 3rd Party Data Synchronization project was completed within the estimated time along with proper QA testing. Since then the customer is regularly synchronizing data via the OMS data synchronization project. He is delighted because now, with just a single click, they can sync all their data.
Comments are closed.