dts queuing retail SQL Server

Synchronizing data from SQL Server

In this instalment I shall speak about a scenario which I solved for a retail client of mine.

The client’s system ran on SQL Server 2005 with an Access 2000 front end, which was used by the product staff, the buyers and the merchandisers, to create and manipulate products. These creations and changes then needed to be synchronised with the warehouse fullfullment system which tracks these products and creates the shipping and packing documents when they are sent out. The warehouse system did supply a odbc connection driver, but it was read only.

We had to think a bit because obviously we would not want each and every time a user went on the new product screen to engage in synchronisation. The server would be engaging in synchronisation on a constant basis. As well when a product is created, sometimes only the SKU and other information are known but other information such as barcode and packsize and price aren’t. So there needed to be a way to ‘bookmark’ a product before sending it to the other system.

Our solution was to create a two stage product creation. In the first stage the user creates the product and obtains a SKU. Once all the information is available then the user can submit the product to the warehouse system and propagate the data to all the stores and tills. We accomplished this using a simple queuing table and DTS packages.

When a user submits a product it will get noted in a general queuing table. Every 15 minutes a DTS package would run and create a special delimited file which was then ftp’d to a special folder on the warehouse system. That system then had a daemon (it was based on Linux) which then picked up the file and made the appropriate additions and changes. The queuing table would then get run down and each product would be inserted into each store ready to track the stock and into a table which would be used to build the nightly update file for the tills.

We found the usage to these tables and DTS packages a superb and simple way to queue up data and free the user’s system immediately. Errors could be collected and sent to a administrator user who could then rectify any problems.