run query using a Lookup?

[Cirrus9]

I have a question about updating from a lookup in a DTS Package.

I have a DTS that populates a table by moving about 12k records from the exact table structure in another database. Between DTS updates, some of the records in the table may be updated by users. I have a lookup where I have isolated the records that have been changed by users.

When the records are imported again, I need to somehow update the records by using the lookup records. The original records are sent over because the DTS does a drop/recreate and deletes everything.

I am new to using the DTS this way. So if you can tell me what features to use and in what way,. I think I can make the query work. I doubt that I have been clear I hope I can explain it better this way:

1) DTS executes and drops table
2) Table is recreated and original records are written with some new ones added. It also overwrites the modified records with original copies.
3)Clients modify these records on occasion so a lookup has been created to identify which records have been modified (since modifications need to be kept)
4) I'm Stuck----need to make lookup re-update records with modified information. I really don't know the best way to approach this or the features to use.


Any help would be appreciated. Thanks in advance!

 

[TheBugSlayer]

Cirrus9,

Updating the same records with your clients' (customers') changes each time the DTS recreates the tables could get time consuming. Besides, you will have to apply a history of changes to the same records each time the package runs. I think a transactional replication will work best for you: changes are propagated to the other database(almost) as soon as they occur.

I am also thinking that you could update the original database with the clients' changes before each DTS, unless you do not want to modify that one.

 

[Cirrus9]

The database comes from a DOS based system that uses an MSAccess back end. It is export only because it draws from about 20 different databases. to create the export access db that is brought into SQL. It runs each night in the middle of the night. The reasons why we chose to rewrite vs. append is the failure we encoutered because we are working across internet domains.

Writing to the Access db is something that I seriously considered and it made a lot of sense because this particular database can be be updated before running against the SQL database.

I understand how to set up the DTS package to run against the access db but I don't seem to be using the right service or using the query in the right place or way to make it happen. I just need it to conduct a simple Update query. Find the primary key and update * where primary key = primary key.

If I can successfully achieve this against the access db before it imports then that would be great. The SQL and access table structures are identical. I don't know why I am having trouble with this, it seems like such a simple procedure.