Copy SQL Server Objects Problem

[BigKahunna]

I am trying to copy data from one database to another using a DTS package I created. All the steps in the package work until it gets to the final stage and runs the Copy SQL Server Objects module to copy data from one database to a new structure in another The problem that I am having is that both the databases in question have a handful of tables that do not exists in the other. I can get round this when using the DTS wizard as the copy tables option has some inteligence build in, in that if a table doesn't exist in the destination it creates it and then copies the data. However the Copy SQL Objects stage either throws up an error that the destination object doesn't exists (When just copying data) or throw up an error that the table already exists (when copying tables+data). I've tried the drop tables option and this doesn't seem to work either. I don't want to have to keep altering the package if possible as I want to leave it set up on clients servers. Bit of a long winded question, but I hope someone can help!

 

[dpostman]

this bid of sql should take care of the table already exists problems. I believe if you set up your package to drop and recreate the tables it will produce this automatically before the create statement. (put your table name in place of YourTableNameHere)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[YourTableNameHere]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table YourTableNameHere
go

 

[BigKahunna]

I need to keep the format of the tables that do exist in the destination database since this is a new (latest version) of the database structure. I suppose what I really want to do is use the copy data only option, but skip those tables that are in the source database but not the destination (these will be tables that are no longer needed and therefore do not need the contents copying over). Thanks in advance for any help on this one!

 

[dpostman]

Not sure about doing that with the wizards, but that same if exists statement, can probably be used to control the copies.