BUG: DTS May Return a Success Even When it Fails to Transfer Data

The Transfer SQL Server Objects task of Data Transformation Services (DTS) may return a success even if it fails to transfer the character data into the destination database. This may occur when you try to transfer the character data into a column in which a character data type (char or varchar) is smaller than the column of the source table. If the data of the source table is larger than the column of the destination table DTS may output the following error message to the <dbowner>.<table>.BCP file on the script file directory:

To work around this problem you can either:

• Use the Drop destination objects first option in the Transfer SQL Server Objects Task.
  
  -or-
  
  
• Widen the column of the destination table so that it is larger than the column of the source table.

Microsoft has confirmed that this is a problem in Microsoft SQL Server 7.0.

This problem also occurs when you use the Copy objects and data between SQL Server database option in the Data Transformation Services Import/Export Wizard.

Steps to Reproduce Behavior

1. Execute the following SQL Server scripts to create a sample database:

   use master
   go 
   create database db1
   go
   create database db2
   go
   use db1
   go
   create table t1 (c1 int not null, c2 varchar(4))
   go
   insert into t1 values(1,'aaaa') 
   go
   use db2
   go
   create table t1 (c1 int not null, c2 varchar(3))
   go

2. Execute the Data Transformation Services Wizard.
3. Click Next.
4. Under the Choose a Data Source tab, select the following options, and then click Next:
   Microsoft OLE DB Provider for SQL Server
   Server: <Your Server>
   Use Windows NT authentication
   Database : db1
5. Under the Choose a Destination tab, select the following options, and then click Next:
   Microsoft OLE DB Provider for SQL Server
   Server: <Your Server>
   Use Windows NT authentication
   Database : db2
6. Under the Specify Table Copy or Query tab, select Transfer objects and data between SQL Server 7.0 databases, and then click Next. button.
7. Under the Select Objects to Transfer tab, select the following options, and then click Next:
   Create destination objects (tables, views, stored procedures, constraints, and so forth)
   Include all dependent objects Copy Data Replace existing data Transfer all objects User default options
   NOTE: Do not select the Drop destination objects first check box.
   
   
8. Save this DTS package, and then run the package immediately.