> 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.
>