Data import from .txt - Design Question

[nakkii]

Background:

We have a very old legacy database that uses flat files, this database is our primary customer repository. We export text files every half hour (at the top and bottom of the hour) from this database and then SQL runs a DTS package every half hour (at the 1/4 and 3/4 hour) that imports this data.

Our SQL DB currently consists of two sets of tables, temp and production. The DTS purges the temp tables and imports the text files into the temp tables, it then runs a stored procedure that does a data compare and adds/modifies/deletes records in the production tables so the data is current (matches the temp tables). We have two issues that need to be resolved, 1) locking during the import/update and 2) several triggers need to be added to the production tables for automation purposes.

What I am going to do is setup a new DB and then replicate the production tables in the import database production tables to the new DB, and then put the triggers on the tables in the new DB.

This will solve:
1. Locking, because the locks will be in the new DB and replication will handle those issues without crashing the import processes.
2. Triggers will fire on a per update vs. trying to fire on bulk inserts (from the import, currently to create some of the triggers we are needing to use complex cursors to accomplish the tasks).

My Question: Is there a better way to accomplish this? Am I making things to difficult/complex?

Thanks in advance for any input, guidence, help.

Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge

 

[macleod1021]

If Im understanding this correctly, your users are updating the old legacy database and then you move the updates into the SQL Server where the users are updating the data????

If they're using both, then you should consolidate them to one system. That will solve the import problems.

As far as using replication to prevent the locking, etc. I don't see a technical issue with it other than you're adding yet another step to a long drawn out process. Whenever you add more steps, you increase the chance of errors and ultimatley possible corruption. Personally, I would push heavily for all users to be on the same system.

 

[nakkii]

Consolidation is not an option. Also the users are not updating both sets of data, they only update the legacy system, but the changes made in that data is triggering data changes in newer systems and is triggering automation of business processes.

I understand this is adding another step in the process, that was why I posted the question to see if there is a better way (other than consolidation, we must keep both systems).

Just for better clarification here is a simple block diagram:

Legacy Data -> Exported Text Files -> SQL DTS -> transform data -> import into temp tables -> SP compares temp table to static tables -> replication replicates changes from static tables to production tables.

We must maintain both systems. The data in production on the SQL server must accomodate triggers w/o complex cursors (the bulk insert from the DTS causes multiple records to fire the trigger).



Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge

 

[nakkii]

One more clarification: The legacy system contains close 1TB of data (not to mention all the custom user interfaces) of which only approx 1GB is being exported (hence the reason consolidation not an option) and all new systems are being done in SQL 2000 (soon to be 2005).

Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge