Realtime transformation for 1 to many tables

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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:

    http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=2526&FORUM_ID=19&CAT_ID=3&Topic_Title=Linked+Server+%2D+Poor+performance%3F&Forum_Title=Data+Transformation+Services+%28DTS%29

    thanks

    Craig

  • 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

    steve@dkranch.net

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply