PRB: DTS Copy Table Uses Table Schema of Existing Table (247825)

CAUSE

This behavior is normal and by design. The wizard correctly creates the destination table as indicated, which defaults to the existing table's schema. Unlike object transfer, the wizard does not overwrite the destination table with the source table schema. You can edit the columns in the destination grid and change the destination schema default or change the SQL directly for the destination schema.

MORE INFORMATION

Steps to Reproduce Behavior

Create two databases, DB1 and DB2, each containing Table1 with different schemas.

You can use the following TSQL code to create the table schemas:

/* Drop Object:  Table[DB1].[dbo].[TABLE1]*/ 
     Use DB1
     go
     if exists (select * from sysobjects
        where id = object_id(N'[dbo].[TABLE1]')
        and OBJECTPROPERTY(id, N'IsUserTable') = 1)
        drop table [dbo].[TABLE1]
     GO
     /* Create Object:  Table[DB1].[dbo].[TABLE1] */ 
     CREATE TABLE [dbo].[TABLE1]([First Name][char](10),
         [Last Name][char](10))
     GO
     /*  Can Insert Data into Test.dbo.table1 */ 
     insert dbo.TABLE1 values ('Joe', 'Smith')
     go
     /* Drop Object: Table[DB2].[dbo].[TABLE1]*/ 
     Use DB2
     go
     if exists (select * from sysobjects
        where id = object_id(N'[dbo].[TABLE1]')
        and OBJECTPROPERTY(id, N'IsUserTable') = 1)
        drop table [dbo].[TABLE1]
     GO
     /* Create Object: Table[DB2].[dbo].[TABLE1]*/ 
     CREATE TABLE [dbo].[TABLE1]([FavoriteDay][char](10),
	[FavoriteAnimal][char](10),[FavoriteNumber][int])
     GO
     /*  Can Insert Data into Test1.dbo.table1 */ 
     insert dbo.TABLE1 values ('Sunday','Dog',13)
     go

NOTE: The test data is not necessary but it helps to better illustrate the point of this problem.

Start the Data Transformation Services (DTS) Wizard. Select DB1 as the source database, select DB2 as the destination database, and then connect to the databases.
Select the Copy table option. Select Table1, and then click Transform.
Select the Create destination table and the Drop and re-create destination table check boxes.
Run DTS.

The DTS package should complete successfully, drop the table, and enter data from DB1.dbo.table1 into the wrong schema from DB2.dbo.table1.

Special Considerations

You might not discover this problem until you attempt to insert new data into the new table. This is because you may not notice the change in the table schema at first on very large tables, which have similar table schemas of the table being replaced.