locked
SQL Server Migration Assistant 2008 RRS feed

  • Question

  • I plan on using this tool to migrate an Oracle database to SQL Server 2008. Just to make sure, the original Oracle database will not be deleted or altered in any way correct? All this tool does is make a copy of the tables/packages/procedures to SQL Server?
    Friday, October 23, 2009 4:54 PM

Answers

  • The Import Export wizard can point to an Oracle server to list the tables you want to move, and then allow you to copy all those to new or existing tables at the destination SQL Server. It builds SSIS packages underneath, one step in Execute SQL Task to create the destination objects, and the rest of the steps are dataflows to copy data from the OLEDB Source (Oracle) to the OLEDB Destination (SQL Server).

    SQL 2008 IE Wizard has enhancements around the Import Export Wizard to allow a better experience, and it allows you to add conversions in if there is a datatype mismatch between the source and destination systems. The mapping files for OracleToMSSQL10.xml and OracleToSSIS10.xml in the folder C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles influence how the oracle data types are compared to the SQL data types.

    I dont' think the IE wizard can handle Stored Procedures in Oracle though.

    Start the wizard by looking for the icon in the start menu under Microsoft SQL Server 2008, or right click on a database in object explorer pane of Management Studio > all tasks >  import data.

    Microsoft offers an Oracle connector for download here
    http://www.microsoft.com/downloads/details.aspx?FamilyID=d9cb21fe-32e9-4d34-a381-6f9231d84f1e&DisplayLang=en

    Thanks, Jason
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    • Marked as answer by Tony Tang_YJ Wednesday, October 28, 2009 9:27 AM
    Wednesday, October 28, 2009 4:43 AM

All replies

  • In general yes you are right
    but yo mentioned  ...... All this tool does is make a copy of the tables/packages/procedures to SQL Server .....
     copy packages from a source DB the way you are discribing it NO .

    SSIS is mainly for data transfer, but you can use some other objects within it like BACKUP, REINDEXING and generally MAINTENANCE etc...

    but it wont delete and thing unless you use a script that dose
    sh


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Friday, October 23, 2009 5:06 PM
  • Is it possible to use only SSIS to script the tables and stored procedures from an Oracle database to SQL Server? So essentially, not using SQL Server Migration Assistant 2008?
    Friday, October 23, 2009 5:37 PM
  • The Import Export wizard can point to an Oracle server to list the tables you want to move, and then allow you to copy all those to new or existing tables at the destination SQL Server. It builds SSIS packages underneath, one step in Execute SQL Task to create the destination objects, and the rest of the steps are dataflows to copy data from the OLEDB Source (Oracle) to the OLEDB Destination (SQL Server).

    SQL 2008 IE Wizard has enhancements around the Import Export Wizard to allow a better experience, and it allows you to add conversions in if there is a datatype mismatch between the source and destination systems. The mapping files for OracleToMSSQL10.xml and OracleToSSIS10.xml in the folder C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles influence how the oracle data types are compared to the SQL data types.

    I dont' think the IE wizard can handle Stored Procedures in Oracle though.

    Start the wizard by looking for the icon in the start menu under Microsoft SQL Server 2008, or right click on a database in object explorer pane of Management Studio > all tasks >  import data.

    Microsoft offers an Oracle connector for download here
    http://www.microsoft.com/downloads/details.aspx?FamilyID=d9cb21fe-32e9-4d34-a381-6f9231d84f1e&DisplayLang=en

    Thanks, Jason
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    • Marked as answer by Tony Tang_YJ Wednesday, October 28, 2009 9:27 AM
    Wednesday, October 28, 2009 4:43 AM