I have a 3rd party application that stores its data in an Access .mdb
file. I need to transfer this file to SQL Server several times during
the day for some reports that are tied to other data on the SQL Server.
I have set up a DTS job that does the following:

1. Removes the data from the existing SQL table (SQL Command)
2. Copies the data from the MSAccess connection to the SQL connection
(Data Transformation)

This works fine if there is a limited amount of data to transfer.
However, some of my tables that I transfer of upwards of 200,000 rows of
data that need to be transferred and between the time it deletes the
data and finally commits the new data, the table contains no data in the
SQL Server.

I'd thought perhaps if I ran the package as a transaction this would
help, but either I'm doing this wrong (using the "Use Transactions"
checkbock on the Advanced tab of Package Properties) or it doesn't do
what I hoped. There is the "Transaction Isolation Level" dropdown that I
currently have set to "Read Committed", but from what I've read on the
other options, this may not help me, either.

Can anyone give me some direction on this? If there was a way I could
update the SQL database from the Access database instead of reloading it
completely, that would be even better. I have to account for insertions,
deletions, and updates, however, so can't just look for records with a
key that didn't exist before.

Thanks.

Hello chetsjunk,

Have you tried setting up the Access Db as a linked server to the SQL Server
or have you tried using OPENDATASOURCE()? You are right that emptying and
refilling can get impractical quite quickly.

The thing about incremental loading is you have to be able to identify the
rows in the source and the destination and be able to tie them together.

New rows would be key not in the destination but in source
Deletes would be key in destination not in source
Updates would be key in source and destination. (requires logic to determine
a change on an attribute or a blanket update)




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com