RATE THIS ANSWER
-1
Click to Vote:
0- -1
Last Answered:
Jul 30 2008 6:11 PM GMT
by Crunchie
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.
SQL Server 2005, Oracle 9i, Data migration, SSMA, SSIS
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? 
