Read automatically constraints with Data Transformation Services

[TheLoneStar]

Hi!

I'´m trying to migrate a oracle database to an ms sql server 2000 one. I've tried it with the help of Data Transormation Services Import/Export tool. But the resulting tables neither have primary key nor foreign key constraints. I've even tried different ODBC/OLE DB drivers, but no chance. So what is wrong or what do I have to do to get constraint information automatically?

Thanks for any help

Regards,

thelonestar

[rmiao]

You may need script them on Oracle then recreate with script on sql2k.

[TheLoneStar]

Thank for your answer.

What do you mean by scripting them on oracle? Do you mean to retrieve the sql statements from oracle? Is there a way to do this? If so, then I will probably get a problem with the different column data types.

[rmiao]

I don't know much on Oracle. But once you get script, you can modify it with t-sql syntax and ms sql data type.

[skhanal]

DTS does not import constraints or indexes.

You can generate script for Oracle objects (like constraints and indexes) using OEM or any other third party tool. Or you can write your own SQL to generate script by querying catalog tables (DBA_CONSTRAINTS, DBA_INDEXES etc).

The script you get out of Oracle may not run in SQL Server as it is. The syntax is slightly different, so you need to modify them.

[TheLoneStar]

This is exactly my problem. I want to avoid changing datatypes manually. There are too many tables in my database and it was cost a lot of time. There are commercial tools like SwisSql which exactly do this. I wrongly thought DTS can do this...

[rmiao]

DTS can do that between sql servers, but not smart enough to do that between different rdbms.