SSMA or SSIS for migration from Oracle 10 to SQL Server 2008 R2

Question

We are planning to migrate an Oracle 10g database to SQL Server 2008 R2.
At the moment nothing is implemented in the target database and this will give us the opportunity to change and improve the existing schema during the migration.

Not only the data, but also stored procedures and views have to be imported.

I already worked with SSIS and I found an excellent product for data manipulation.
A colleague mentioned SSMA for the migration. However after some research on the net it seems that it would be suitable mainly for data migration and conversion, while SSIS seems to provide a wider set of functinalities (Tasks, custom scripts, etc).

Which are the pro/contra of the two products and which one would best fit for the task?

Answer 1

I would recommend a hybrid approach. Use SSMA to convert the schema and objects from Oracle to SQL Server. Then improve and or change the objects as you see fit on the SQL end. Once your satisfied with your new schema. Use SSIS to move the data still waiting on the Oracle side into the new schema waiting for it on SQL.

As for a quick comparison of SSMA and SSIS... SSIS is by far superior for the ETL aspects of moving data from one place to another; but I wouldn't necessarily recommend it for the creation/modification phase of what you describe above. I think you'll find that process much easier with SSMA. On the flip side SSMA doesn't offer much in the way of transformation during the copy process.