January 11, 2002 at 6:32 am
hi,
I'm working on the architecture design for a project which will involve normalising data tables from an old Clipper/DBF system. What we plan to do is import the data (via some custom written code - standard INSERTs) from the DBF's into a staging SQL database which will be an exact replica of the DBFs. As the data comes into the staging area we want this to be de-normalised into the new structure. Heres an example:
OldCustomerTable
----------------
customerid
customername
bankname
bankid
branchname
branchid
accountid
New Structure:
==============
CustomerTable
-------------
customerid
customername
CustomerBank
------------
customerid
bankid
branchid
accountid
Bank
----
bankid
bankname
Branch
-------
branchid
branchname
So.. when a new record gets inserted into OldCustomerTable this will trigger inserts into the NewCustomer, CustomerBank and perhaps Bank and Branch.
Now this could all be achieved by using triggers but that would require writing alot of manual code. So my questions are:
- are there other ways of achieving this?
- can one call DTS packages from within a trigger like a function?
any advice would be greatly appreciated.
thanks
Craig
February 6, 2002 at 4:36 am
There is a trick to do the job but ...
You create a table (ex.: working table) with a datetime field.
You create a trigger that insert the getdate() value into that table.
the you scheduled your package every minute.
In your package you create a dynamic task property, select the first step of your package and set the disable job as a query like this one:
declare @LOG_DATE datetime
set @log_date=(select max(LAST_DATE_RUNOK)) from WORKING TABLE
if @log_date > select dateadd (mi,-1,getdate())
SELECT 0
else
SELECT -1
Do not forget to set the dynamic property to be the first step using the workflow properties.
Good luck and tell me if OK or not
February 6, 2002 at 6:40 am
Not sure I would tackle it via triggers. Why not just import the data first, then apply a DTS package (or code as you prefer) to do the transform all at once?
Andy
February 6, 2002 at 7:14 am
Hi,
Just to elaborate on the architecture that we're thinking of and the order of events:
Clipper dbfs -> Clipper Staging dbfs -> SQL Staging -> SQL Replica -> SQL New table structure
1. As data gets written/updated/deleted to the live clipper dbfs a process (managed inside the clipper code) will write the record to an exact replica dbf.
2. A VB program will (via a scheduled job) DTS the clipper staging dbfs into the SQL Staging area.
3. The VB app will then transfer the data from the SQL Staging Area to the SQL Replica area. These two databases will be exactly the same structure. I plan to do this via INSERT and DELETE statements so that as new data comes into SQL Replica a trigger on these tables can add the new data to the SQL New table structure.
So what we're really trying to achieve is a clipper to SQL replication (in a new table structure). Also given the table sizes (some dbfs are 600mb large with 4-5mill records) we need to be doing incremental updates and cant simply take the entire dbf. Thats why we're using the clipper staging area which only contains the updates since the last replication.
Its a bit of a laborious process but we havent been able to come up with a better alternative.
The reason we've thought of using triggers is that we can be sure if a record is deleted/inserted in the SQL replica database we can be assured that it will get itself into the SQL New table structure.
Any comments or suggestions will be appreciated.
thanks
Craig
February 6, 2002 at 11:18 am
Insert/delete makes sense rather than trying to track column level changes. Writing your own replication is a lot of work! Overall the plan seems ok. Is dumping the front end app/remodeling to use a SQL table directly out of the question? You could use the same data structure, just write directly to SQL?
Andy
February 6, 2002 at 11:21 pm
Unfortunately dumping the front end app is not really feasible given that we've got 20-25 man years of clipper code.. (and alot of it is mission critical).
We have been experimenting with linked servers as another method of getting the data in and hopefully you can shed some light on a new post of mine:
thanks
Craig
February 7, 2002 at 10:28 am
I've done something similar, but I include a datetime field in my sql staging. Then I track when the process runs and only work with rows that have a timestamp greater than the last time the process ran. Otherwise this looks ok.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply