datatype conversion from oracle to sql whilw using SSIS

Question

datatype conversion from oracle to sql whilw using SSIS

rd2718

Old Hand

Points: 330
More actions
November 16, 2010 at 11:01 am

#143549

hello friends
i am using SSIS for migrating Oracle to SQl 2008 migration.
My first question is which transform should i use to convert oracle data types to the sql datatypes?
Second question is i am already experimenting the SSIS on test server what i figured out is i am getting very weired schema. So how shd i map oracle schema to sql server schema?
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

You can transform the data types directly at the source by writing a proper SQL statement, or you can convert them using the data conversion component.

Regarding your 2nd question: I didn't understand it quite well. What do you mean with "weird schema"?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rd2718 Old Hand Points: 330 More actions · Answer 2

thanks man for replying

I use data conversion task i am able to convert the data types but it started another issue that i am getting double the number of columns like ACTIVITY column i am getting ' copy of ACTIVITY' BUT SAME DATATYPE. Same thing i am getting for other columns. Is there any thing wrong with settings at data conversion transform?

and about the second question actually on oracle side my tables are under schema like MIC and name of my sql server 2008 is MOM-MSDBSQL089.kom.us\Affinity but the table name which i am getting on sql side is MOM\my login.tablename.

So its mapping to MOM\my login schema. How should i change that into dbo schema?

These are two issues.

Thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

* data conversion: when you read the data from the source, SSIS will map it to its internal data types. If you write the data to the destination in SQL Server, SSIS will convert the data from its internal data type to a SQL Server data type. If all these data types are compatible, you don't really have to do anything. But yes, it is normal that you get 2 column in the data conversion component. This transformation takes a copy of the original table and places the converted data in the 2nd column, while remaining the first. Just rename the 2nd column to something useful and make sure you use the correct mapping in your destination.

* schema: create the tables in the schema that you want in SQL Server and choose those tables in the destination.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rd2718 Old Hand Points: 330 More actions · Answer 4

Thanks man

I did the same way as you told by making the table first in the sql Db and this worked.

But i have round 200 tables on oracle side it will be long task is there any way to make it less time consuming?

Thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

You can try the import and export wizard in SSMS. I thinks it allows you to select multiple tables and to create tables if they don't exist yet.

I'm not sure it works with Oracly though, but give it a try.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP