• Question


      I want to know the best way to migrate tables from Oracle 9i
    to SQL Server '08 R2 as a cutover i.e. NO updates, no CDC (Change
    Data Capture) with Attunity. Just SELECT INTO the SQL Server tables,
    which DO NOT yet exist and shutdown the Oracle server for good.

      I do not see any sample SSIS pkgs which do a SELECT INTO where
    tables on the SQL Server do not yet exist.

    Thank you,

    Monday, November 29, 2010 9:41 PM


All replies

  • if you want a ONE-TIME migration procedure, use IMPORT/EXPORT wizard, check tables you need, check mappings, and run it simply.

    but if you want to use select into ... commands,you can use execute sql task in SSIS .

    I think in your situation Import/Export wizard will be better. you can find it with right click on database name in SSMS and select tasks and then import/export data

    Monday, November 29, 2010 9:45 PM
  • Why not to use SQL Server Migration Assistant (SSMA) 2008 for Oracle ?
    Arthur My Blog
    By: TwitterButtons.com
    Monday, November 29, 2010 9:45 PM
  • 167 pgs of instruction for . . .

    "The free Microsoft SQL Server Migration Assistant (SSMA) for Oracle speeds the migration process. SSMA for Oracle V4.0 converts Oracle database objects (including stored procedures) to SQL Server database objects, loads those objects into SQL Server, migrates data from Oracle to SQL Server, and then validates the migration of code and data."

    I really wasn't planning on investing a lifetime (I'm running short as it is).

    Have you actually used SSMA ?



    Tuesday, November 30, 2010 1:40 AM
  • To do the "SELECT * INSERT INTO" you may want to simply link to the Oracle database from within the SQL Server (thus you do not need the SSIS), but I am almost certain it will not be a smooth ride. Tools as SSMA were not created just for the sake of publishing a long document. Did you think about the Collation, defaults, constraints, indexes, etc.?

    I am surprised such a big undertaking is being treated as an ad-hock daily/casual operation.


    Tuesday, November 30, 2010 2:30 AM
  • I would look into the import/export wizard that comes with SSMS first. You may run into some data conversion problems (e.g. date ranges in Oracle are wider than in SQL Server).

    I did not know about SSMA but it may be worth a shot if it helps with data type validations.

    Help Others! Don't forget to mark your thread as answered

    Rafael Salas - SSIS and more
    Tuesday, November 30, 2010 2:35 AM
  • Nod to Odalia for smacking me up side of head . . . you are so right gal . . . so I'm printing 167 pgs . . . DUPLEX !

    And to Rafael . . . I've experienced some of the date width issues . . . i.e. the problem where the last 30 seconds of each day get rounded up to next day where smallDateTime is target (logical really, but be aware, liability of smallDateTime).

    I've experimented with ODBC etc. with Import/Export Wizard inside SSMS . . .

    SSIS . . . which looks like it'd be quite a bit of work in itself by the time esch column mapping is addressed . . .

    Now thanks to ArthurZ for pointing me to SSMA . . . this is looking good (reassuring to see the SS '08 splash screen)(but how is it possible that such a tool exists ? and hasn't been throttled in litigation ??)(well never mind I'm using it while the using is good). I did not know about SSMA. Is it new with R2 ? or has it been around since 2K !!!

    is there a thread or forum devoted to SSMA ?

    any SSMA advice or cautions or pitfalls ?



    Wednesday, December 1, 2010 7:30 PM
  • Glad to hear you are happy.

    It is new and built because there are many migrations from Oracle to SQL Server - this tool aids in the process.

    There are SSMA blogs: http://ssmablog.blogspot.com/ (community) and MS Team's http://blogs.msdn.com/b/ssma/default.aspx?wa=wsignin1.0, go share your experience!

    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, December 1, 2010 7:56 PM