I'm using DTS to import an Oracle DB to SQL server.
Although I know I can specify a non-default datatype transformation on a
column-by-column basis, I want to know if it is possible to globally
transform from one datatype to another.
Here is what the client is requesting:
If DTS encounters Oracle datatypes of NUMBER, size 1 or 5, it should become
SQL Server's "smallint" datatype.
If it encounters the Oracle datatype of NUMBER, size 10, it should become
int.
And if it encounters NUMBER (no size) it should transform to float(8).

The default DTS transformation from the above Oracle datatypes is to a
decimal datatype (with varying sizes, scales and precisons depending
on the input). There are hundreds of tables, so the column-by-column
transformation designation would take some time. But even if I did it
manually the first time, the client has said that they'll need to do this
often,
and the databases won't have exactly the same tables or columns each time,
so it doesn't seem like a DTS package could help me out. But a global
datatype transformation specification would be great--is it possible? Thanks!
--
Kathy

Unfortunately I do not think this is going to be possible dynamically
(well not without a lot of work.)

You would need to

Read the provider details of the columns
Have a mapping table that translates the source datatypes to their
requested equivalents on the destination.
Create the destination tables accordingly
Build the datapump tasks programmatically.


The problem with this is that it would be you doing all the work. You
would not be able to take advantage of the wizard geenerating the create
table statements and mappings etc.

Could get messy

Allan


"Kathy" <Kathy (AT) discussions (DOT) microsoft.com> wrote