Re: DTS package

#1

OK What I would do is this

On the SQL Server have a linked server pointing to your Access DB.
You then issue UPDATE statements for those rows that have corresponding key
values and INSERTs for those rows that are shown as new but for which there
is no existing same key value in the Access DB

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Ann" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

I'm new to dts. We have a web application to collect data
from clients then insert into SQLserver.
On a nightly basis, we will transfer the new data to our
internal Access database.

On the SQL server, we have a table with a column called
newest sumbit date. We use this field and joined with
other tables to find out which IDs are newest records.
Then we will run at midnight the dts, that will delete
old records with the same ID numbers in our Access
database and transfer the new records.

What I'm doing now is like this:

1. create some views in SQL server that has the newest
records tables.
2. send one of the tables called newest IDs to the Access
table.
3. in Access conection, delete those records in other 3
tables with same IDS.
4.transfer the newest records (data in the views)to the
other 3 tables

Am I on the correct way?
I wonder what order it will process the DTS, I added some
workflows arrows there, but there are some transfer data
tasks there, use the same connections, I just cannot
figure out what order it is in DTS?

Thanks for any suggestions

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: DTS package

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: DTS package - 12-11-2003 , 04:13 PM