I need to migrate data from one sql database to another. The second DB
is a newer version of the "old" database with mostly the same tables
and fieldnames. In order support some reporting queries in the "new"
version I needed to change the datatype of a few fields from varchar to
int(the data stored was integers already as they were lookup tables).

DTS works great except in the cases of about 10 fields which I changed
the datatypes on from varchar to int.

DTS seems to drop the data if the fieldname and datatype are not an
exact match. Is there any way to use DTS and have it copy data from a
field call subsid type varchar to a field call subsid type int?

<tdmailbox (AT) yahoo (DOT) com> wrote

This should work fine - I tested it quickly on two tables with the same
column name but the source being varchar(10) and the destination int. Using
a Transform Data Task worked correctly, and the mapping was handled
automatically.

I'm not sure what you mean by DTS seems to "drop the data". Perhaps you can
give some more information - your MSSQL version, which type of task you're
using to move the data, the DDL for the tables, any error messages etc.

Simon

DTS copies all the columns except the ones with datatype changes. My
varchars to ints end up with empty ints in the desitnation table.

SQL 2000, DTS export, no error messages.

I tested it again with the export wizard (I'd used the package designer
before), and it worked fine. I have no idea why it's not working for
you, unless you have a column transformation of some sort. One other
guess would be that your values are too big for an int, and you've also
set ANSI_WARNINGS OFF, which would ignore the overflow. But since
that's ON for ODBC/OLE DB by default, it's very unlikely.

You could specify a source query instead of a source table and use CAST
to force the conversion:

select col1, col2, cast(col3 as int), col4...
from dbo.SourceTable

Even if that doesn't work, you might get a clue of some sort from an
error. Finally, you could also copy the data into a staging table which
has exactly the same structure as the source table, then use SQL to
check and INSERT the data. Or just INSERT directly, if both databases
are on the same server.

Simon