Data transfer from sqlserver to Oracle

• adisql

  SSCrazy

  Points: 2893

  More actions

  August 19, 2020 at 8:05 pm

  #3781775

  Hi ,

  Source : SQL Server Table  ;  Destination : Oracle  Table

  What is the best way to schedule the data transfer when new record inserted in source(sql server table).

  Thank you for your suggestions.

  Thanks.

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  August 20, 2020 at 3:03 pm

  #3781983

  SSIS, SQL Server Integrated Services.

   

  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e9b0a27b-6819-4c04-8e18-f8aaf01f4678/etl-from-sql-server-using-ssis-to-oracle-db-11g

  MCSE SQL Server 2012\2014\2016

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737475

  More actions

  August 20, 2020 at 4:44 pm

  #3782012

  Is this needed in real time? As in a replication type scenario?

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  August 20, 2020 at 5:49 pm

  #3782032

  you could also set up a linked server see mssqltips.com articles on this,

  https://www.mssqltips.com/sqlservertip/4414/transferring-data-between-sql-server-2014-and-oracle-11g-databases/

  https://www.mssqltips.com/sqlservertip/4430/faster-way-to-transfer-data-from-sql-server-2014-to-oracle-11g/

  If this is a real time transfer I think replication might be an option as Steve Jones had questioned earlier in the post. I have seen some posts on it, it's worth a google to find the article you are looking for. There is even one in the google search from a previous post on sql server central.

  https://www.sqlservercentral.com/forums/topic/sql-to-oracle-replication

   

  • This reply was modified 4 years, 7 months ago by  lkennedy76.

  MCSE SQL Server 2012\2014\2016

• adisql

  SSCrazy

  Points: 2893

  More actions

  August 20, 2020 at 5:59 pm

  #3782035

  yes , it is required in real time.

  when a source table has any insertions that should be transfer and update at target table.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737475

  More actions

  August 20, 2020 at 7:05 pm

  #3782062

  Replication to other platforms is not supported after SQL 2016

  https://docs.microsoft.com/en-us/sql/relational-databases/replication/non-sql/heterogeneous-database-replication?view=sql-server-ver15

  there are third part products to do this, but they cost money.

  You can use a linked server and a trigger, but I would highly recommend against this. Any issue with the linked server and then your transactions will not complete on the SQL Server, which is often a problem.

  Instead, I would track the changes with a column or use a trigger to write the data you want to move to another table. Then have a job, SSIS, linked server, etc. that runs every minute and moves the rows across.

• adisql

  SSCrazy

  Points: 2893

  More actions

  August 21, 2020 at 1:57 am

  #3782132

  Thank you for  valuable suggestions.