What is best way to Migrate Oracle tables to SQL Server 2005/2008 through SSIS

Question

Hi All,

I have the Requirement to Migrate DB (Table Structure & Data) from Oracle to SQL Server 2005/2008 through SSIS. What could be the best way to do this.

Note I don't want any data modification. I don't have tables created in SQL Server 2005/2008 (Destination Server). I want to creat tables on the Fly & then Load it.

---

Thanks Shiven:) If Answer is Helpful, Please Vote

Answer 1

Best way would be to not use SSIS.  Microsoft has a tool called the SQL Server Migration Assistant which is specifically for migrating an Oracle database to SQL Server. (SSMA)

http://www.microsoft.com/en-us/download/details.aspx?id=28766

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 2

Hi,

want to do through SSIS. Please let me know possible ways & Easy method

---

Thanks Shiven:) If Answer is Helpful, Please Vote

Answer 3

Hi,

want to do through SSIS. Please let me know possible ways & Easy method

---

Thanks Shiven:) If Answer is Helpful, Please Vote

There is no "easy" way to do it using SSIS.  Only the long and painfull way, which would require you to recreate your tables and indexes in sql Server is a mostly manual way.  Once you have created an equivilant schema in SQL server then you could write an SSIS package to map the data.

Use the correct tool for the job

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 4

Hi S Kumar Dubey, 

Here is a paper explores the challenges that arise during migration from an Oracle database to SQL Server 2008. It describes the implementation differences of database objects, SQL dialects, and procedural code between the two platforms. Please see:

Guide to Migrating from Oracle to SQLServer 2008: http://download.microsoft.com/download/7/C/2/7C20B070-BFF8-44B4-BD7D-1B03DF50F924/MigrateOracletoSQLServer2008.docx

Thanks,
Eileen

---

Eileen Zhao
TechNet Community Support