none
SSMA for migrating table from oracle to Sql server

    Question

  • Hi All,

    I wanted to replicate oracle huge table to sql server and i am using SSMA.its helpful and fast but can we replicate the table to different name using SSMA.for example i have a table TEST and i wanted to replicate it to SQL_TEST.Can it be possible using SSMA.

    Kindly help me out 

    Monday, April 28, 2014 1:18 PM

Answers

  • Hi Prashanth,

    I used your approach but it failing.Strange thing is that it not showing any error.just it showing status as failed.Do SSMA automatically creates the schema or we have to create it?

    Thanks,

    When you use SSMA to convert Oracle schema to SQL server it is converted as database in SQL server.You need to give name before actually converting it.What account you are using to connect to oracle make sure you use system account . Did you ran reports before actually migrating.Did it pointed out any converison failure. Please note that SSMA tries to convert oracle queries as much possible to SQL server equivalent queries but its not guaranteed it will do it completely.Its long time I interacted with SSMA so might not be exact to the point

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, May 01, 2014 9:25 AM
  • My Apologies Arthurz.

    I was checking for option in SSIS so by mistake i raised the question in this Forum.

    I can't rename the table once done because i have the same table in SQL server and i don't want to overwrite that table.So as an Standard we are trying to repicate it to an another name which our aplication will pick.

    Thanks,



    Then create a new SQL Server based database which would be a 1:1 replica of the production (target) database, but with no data, and shove the data from that Oracle db table to this temp target. This way you avoid the table name issue. As the last step, move the data from this temp db table to the real target.

    Arthur My Blog

    Thursday, May 01, 2014 1:38 PM
  • Hello,

    As shanky  mentioned you need to have proper permission to work with SSMA. If not, it will fail no matter what you do.

    How big is the Oracle table?. If you want to clone one table then create a linked server,do the conversion and load the table which is much simpler OR You can also try Import/export wizard 

    select 
    CAST(col1 as NUMERIC(10)) col1,
    col2,
    col3
    INTO temptable
    FROM OPENQUERY(GENP_OWUSER,
    'select 
     col1,
     col2,
     col3
    from 
    <schemaname>.test');
    --Prashanth


    Thursday, May 01, 2014 2:17 PM

All replies

  • Hi All,

    I wanted to replicate oracle huge table to sql server and i am using SSMA.its helpful and fast but can we replicate the table to different name using SSMA.for example i have a table TEST and i wanted to replicate it to SQL_TEST.Can it be possible using SSMA.

    Kindly help me out 

    Friday, April 25, 2014 7:07 PM
  • Why you cannot rename the table once done?

    How this post is relevant in the SSIS section of the MSDN forum?


    Arthur My Blog

    Friday, April 25, 2014 8:38 PM
  • My Apologies Arthurz.

    I was checking for option in SSIS so by mistake i raised the question in this Forum.

    I can't rename the table once done because i have the same table in SQL server and i don't want to overwrite that table.So as an Standard we are trying to repicate it to an another name which our aplication will pick.

    Thanks,


    Monday, April 28, 2014 1:10 PM
  • Hi All,

    I wanted to replicate oracle huge table to sql server and i am using SSMA.its helpful and fast but can we replicate the table to different name using SSMA.for example i have a table TEST and i wanted to replicate it to SQL_TEST.Can it be possible using SSMA.

    Kindly help me out 

    Hello,

    Same question has  already been asked by you in below thead. Why you created duplicate thread ?  please avoid this practice or your thread will be marked ass Spam

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/abcdfb1b-c617-453f-828d-c8e4ec266c78/ssma-for-migrating-table-from-oracle-to-sql-server?forum=sqlintegrationservices

    Moderators plz merge this thread.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Monday, April 28, 2014 1:46 PM
    Monday, April 28, 2014 1:45 PM
  • Sorry Shanky.

    The question i raised in incorrect forum so i raised it again in the correct Forum.

    I won't do it in future.

    Will i be able to get my answer or it will be consider an Spam?.If so i will Ask an new Question.Kindly let me know.

    Thanks

    Monday, April 28, 2014 2:19 PM
  • Thank you for confirming.Please see original threa don which Arthur was replying.Have patience MSFT CSG guys will reply

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, April 28, 2014 2:39 PM
  • You can try modifying the target_schema and create a new schema and write migrate the table and rename table.

    --Prashanth

    Monday, April 28, 2014 2:41 PM
  • Thanks Prashanth for the quick Response but i can't rename the table after migrate as my SQL server already contains the TEST table and it might overwrite the data.So i wanted to copy the TEST table data onto SQL_TEST.Is it possible using SSMA..

    Awaiting for your response.

    Monday, April 28, 2014 5:19 PM

  • you can modify the target_schema(select the oracle schema, then you can see source and target schema, click modify to enter new schema) and use convert and migrate to a new schema(for example newtestschema).

    for example, newtestschema.test and dbo.test both are different though the table names are same but it belongs to two diff schema and then try to insert the rows from newtestschema.test to dbo.test.

    --Prashanth

    Monday, April 28, 2014 6:41 PM
  • Prashanth new schema means new database in SQL server?.my dbo schema already has an test table so i wanted to copy the test table from oracle to dbo.SQL_test.Can it be done with SSMA.

    I am struck midway and my project is going live and i have this new requirement.kindly help me out

    Monday, April 28, 2014 9:33 PM
  • Create a schema in sql under the database where you want to create the table. Let's say Test is the name of the db and you have created testscheme. If you expand schema, it should list testschema. Once done change the target schema(SSMA) to test.testschema, use convert and migrate the schema. You should see a test table created with testschema.test. Expand the tables, you should see two test tables with different schema .
    Monday, April 28, 2014 10:33 PM
  • I got your Point Prashanth.Thanks for the Suggestion but i want to copy the test table into dbo schema with SQL_test can't it be done straight forward in SSMA.do i have to create an separate schema in SQL server and from there copy the table again to dbo schema with the desired table name??

    Thanks,

    Tuesday, April 29, 2014 1:48 PM
  • Yes, You are right. Either you can create schema in sql or you can directly do it in SSMA(It will ask you a create a one, if its not there in SQL).

    Once you migrate the table, you  can either use insert statement to pull the records from new schema to desired table or you can rename the old table and do the same for the migrated table by renaming it back to the desired table

    --Prashanth



    Tuesday, April 29, 2014 2:10 PM
  • Hi Prashanth,

    I used your approach but it failing.Strange thing is that it not showing any error.just it showing status as failed.Do SSMA automatically creates the schema or we have to create it?

    Thanks,

    Wednesday, April 30, 2014 9:25 PM
  • Hi Prashanth,

    I used your approach but it failing.Strange thing is that it not showing any error.just it showing status as failed.Do SSMA automatically creates the schema or we have to create it?

    Thanks,

    When you use SSMA to convert Oracle schema to SQL server it is converted as database in SQL server.You need to give name before actually converting it.What account you are using to connect to oracle make sure you use system account . Did you ran reports before actually migrating.Did it pointed out any converison failure. Please note that SSMA tries to convert oracle queries as much possible to SQL server equivalent queries but its not guaranteed it will do it completely.Its long time I interacted with SSMA so might not be exact to the point

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, May 01, 2014 9:25 AM
  • My Apologies Arthurz.

    I was checking for option in SSIS so by mistake i raised the question in this Forum.

    I can't rename the table once done because i have the same table in SQL server and i don't want to overwrite that table.So as an Standard we are trying to repicate it to an another name which our aplication will pick.

    Thanks,



    Then create a new SQL Server based database which would be a 1:1 replica of the production (target) database, but with no data, and shove the data from that Oracle db table to this temp target. This way you avoid the table name issue. As the last step, move the data from this temp db table to the real target.

    Arthur My Blog

    Thursday, May 01, 2014 1:38 PM
  • Hello,

    As shanky  mentioned you need to have proper permission to work with SSMA. If not, it will fail no matter what you do.

    How big is the Oracle table?. If you want to clone one table then create a linked server,do the conversion and load the table which is much simpler OR You can also try Import/export wizard 

    select 
    CAST(col1 as NUMERIC(10)) col1,
    col2,
    col3
    INTO temptable
    FROM OPENQUERY(GENP_OWUSER,
    'select 
     col1,
     col2,
     col3
    from 
    <schemaname>.test');
    --Prashanth


    Thursday, May 01, 2014 2:17 PM
  • Hi All,

    I am trying to copy the data into same database but different schema is it possible and does SSMA creates the schema in the SQL server or we have to create it.i am able to copy the data into dbo schema but not into TEST schema within same database so i don't think issue is with Permissions.Also the oracle table size is huge it has 32 millions records i have already tried to use the approach of linked server but it failed for 3 millions only.it gave query timed out error after 2 hours and the same 3 millions i was able to transfer in 8 minutes using SSMA.but now i am getting while migrating the data.the Report doesn't show any error.

    Waiting for your inputs.

    Thursday, May 01, 2014 7:30 PM
  • Thanks a lot Everyone.i am able to copy the data into different Schemas.

    Thanks for your help.

    Friday, May 02, 2014 6:11 PM
  • Thanks a lot Everyone.i am able to copy the data into different Schemas.

    Thanks for your help.


    Please mark the answers accordingly ,or if you found solution by yourself please post it here.In that case other members can take help from that

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    My TechNet Wiki Articles


    • Edited by Shanky_621 Friday, May 02, 2014 7:23 PM
    Friday, May 02, 2014 6:13 PM