Migrating data from Oracle 9i Database to SQL Server 2005

Tags:
Data migration
Oracle 9i
SQL Server 2005
SSIS
SSMA
I would like to migrate the data from all used-defined tables in Oracle 9i Database to SQL Server 2005. I know of options like Linked Server, SSIS, SSMA for Oracle and BCP. Which amongst these is the best way to achieve data migration?

Answer Wiki

Thanks. We'll let you know when a new response is added.

When moving from one platform to another the easiest method is SSIS.

—–
I’d strongly recommend using the latest version of SSMA. SSIS is a very capable ETL tool but when you’re moving between different database vendors, it may be a simple vanilla move or it could involve significant compatibility issues. For starters, you don’t even know if the Oracle schema will have an identical equivalent in SQL Server. If you’re just moving parts of the Oracle database for reporting or other purposes, it’s not such a big deal (and SSIS does a fantastic job) but if you’re migrating the whole DB to SQL Server, SSMA is the tool of choice.

SSMA v4.0 is radically different from earlier versions as far as data movement is concerned. You will see similar performance as SSIS and BCP in moving large amounts of data. This was far from the case in 2.0 and earlier. The added benefit of SSMA is you get to first map the schemas and make sure they are compatible. If adjustments are needed, they are easily accomplished and reviewed before data movement. You can also move constraints, indexes, views and stored procedures with SSMA. Not so with SSIS.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following