As sited in past posts we are shop that is still in the process of migrating off SQL Server 2000. We have several jobs that use copy objects to replace production data with a minimum of downtime. The process flow is similar to the following:
- Load Data into Simple Recovery Database
- Massage Data doing updates/inserts to prepare for production
- Use DTS Copy Object Task to move tables to production database
- Rename current production tables to backup
- Rename copied tables to production name
This process works very well except when two copy object tasks execute in parallel mode. When this happens one of the two copies will fail referencing objects that are not in the database the task is working with.
In Microsoft's wisdom they default the script directory and filename and these are the same for each instance of the copy object task. If multiple tasks execute at the same time the files are overwritten with subsequent runs. When the process goes back to read the file for actions it picks up the new task, using objects not in the current connection.
The developer who was working on a solution to this issue found this URL that explains the situation and a fix:
http://www.sqlservercentral.com/articles/DTS/dtscopyobjectstask/1531/
Hope this will help someone else.
