Copy objects drops table and does not populate again

[dyarwood]

I have numerous DTS packages containing a copy objects task. A few times now the DTS has run but the copy objects job failed to populate the destination table. The source table exists and is populated. I am wondering what might cause this. The databases and DTS are on the same server. The DTS is run from a job which is scheduled daily.

Any assistance would be very much appriciated.

dyarwood

 

[dbomrrsm]

On the copy tab of the task are you sure the copy data box is ticked ?

[blue]DBomrrsm[/blue]

 

[dyarwood]

It is ticked. To be honest I had not checked that since I created the DTS. The package works most of the time its just sometimes the problem happens.

 

[swingheim]

I had the same problem on MS SQL Server 2000 Service Pack 3a, and honestly believe that it is a glitch/bug. I suspect it has something to do with permissions ... that I have different account names on one machine from the next.

After changing every single option, and having it still not populate the tables, I created my own DTS package, and did everything manually.

For my purposes, I did not need to have the id field auto-increment on the destination server, nor did I need to copy any triggers, indexes, etc. ... I just needed the data there.

So, I created 3 objects:

1. SQL Command: DELETE FROM tablename

This cleared out the table of all data

2. SERVER object to SOURCE server

3. SERVER object to DESTINATION server

I then setup a data transformation between the two servers, and straight up copied the table from one to the other. I added a workflow line from the SQL command, so that on SUCCESS, it would then run the data transformation between the servers.

Again, this is rather simplistic, and won't copy triggers, indexes, etc., just the data.


Good luck!