Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

copying tables from Oracle 10g to MS SQL 2005

Posted on 2010-08-30
15
Medium Priority
?
442 Views
Last Modified: 2013-11-30
i wish to do the following:
copy 2 tables (and its data) from an oracle server on machine A to MS SQL server 2005 residing on machine B

this needs to be done using an application whenever the user wants... meaning, an application residing on an client's machine will trigger this copy

please guide
0
Comment
Question by:RakeshBhandari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33556312
0
 
LVL 15

Accepted Solution

by:
AmmarR earned 1800 total points
ID: 33556592
Dear RakeshBhandari

As mentioned by om_prakash_p you need to build an SSIS package to copy Data from oracle to SQL.

its very simple and straight forward process, all you need to do is

1. Install Oracle client 11
2. Use Oracle Net Manager to configure the connection to the oracle server
3. Create an ODBC connection to the Service you created using the Net manager
4. create an SSIS package using the wizard and chose (Oracle Provide for OLE DB)

Check images below in sequence

and download the document i attached, its from microsoft ,and its about SQL Server Integration Services with Oracle Database 10g for SQL 2008, but it will help you and explain things to you even for 2005

check this link as well,
http://www.microsoft.com/sqlserver/2008/en/us/ssis-oracle.aspx

Hope it helps

Regards
Oracle-Net-Manager.jpg
Oracle-ODBC.jpg
SSIS-Oracle-Wizard.jpg
OracleSSIS.docx
0
 
LVL 6

Expert Comment

by:sridharv9
ID: 33561248
For simplicity and to setup script to rerun this automatically on demand. I would go with following steps.

Method 1.
------------
1. Oracle side  = Using SQLPLUS query the tables and spool the data as comma delimited file.
2. SQL server side = Using bcp utility load the data into SQL server tables.

or    

Method 2.
------------
1. Oracle Side = Using SQLPLUS spool the data with "insert into table ..." string along with data.
2. SQL server side = Run the above generated file from ISQL and insert data directly into the SQL server.

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33564643
@AmmarR

thank you very much for the information

i, however, still have to find oracle client 11 on the oracle's site... can you please help me with a link?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33565053
Dear RakeshBhandari

i have it and its about 500 mb, if you have an FTP site, i will upload to you,

here is links if you need direct download

http://www.easysoft.com/support/kb/kb00612.html
http://www.mandsconsulting.com/oracle-client-download-11g-windows-client-32-bit-and-x64

you will see in the page they are pointing to Oracle website.

Regards

0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33565130
i managed to get this : 10201_client_win32.zip ... it is 453mb in size
its link was present in your doc

i think this should suffice?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33565663
Dear

when you run the setup you should see the image below

Regards
oracle11.jpg
0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33565808
yes... i have the similar screen... but with oracle 10g [not 11g]


when you say "Use Oracle Net Manager to configure the connection to the oracle server" what does it mean?
i already have the default TNS listener configured
0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33565830
also... what is this SSMA thing? is it any good/better than what you are teaching me?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33565865
10g should work fine.

oracle net manager actually configures you tnsnames.ora file.

so if you can manually configure the file, then why bother with net manager,

just make sure the entries in the tnsnames.ora file are formatted properly like below
and it should work fine


------------------------------------
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ServerName =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.0.7)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ServiceName)
    )
  )
0
 
LVL 6

Assisted Solution

by:sridharv9
sridharv9 earned 200 total points
ID: 33580043
Q. what is this SSMA thing?s it any good/better than what you are teaching me?
Ans: SSMA is used for migrating db server from non-MSSQL sever (Oracle/sybase/db2/informix) to MSSQLserver. And for your requirement it is migrating data (not server) from Oracle to MSSQL. So follow the AmmaR advice and at end you can choose either SSI or regular SQL queries to accomplish your task.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33584467
As mentioned by sridharv9, i would prefer the simple and normal method of SSIS,

 i tried SSMA before with sybase, and its wasn't as easy and as smooth as they claimed it would be, there are few challenges and pre-configurations that you need to setup before using it.

but if you want to read more about SSMA and try it your self here are useful links

SSMA for Oracle
http://www.microsoft.com/downloads/details.aspx?familyid=3E8722AE-77F3-4288-906D-F421E24D322F&displaylang=en

White papers
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c7933d3e-b7b9-43a6-ade3-f8e37c8cb046#tm

Blog
http://ssmablog.blogspot.com/2009/09/sql-server-2008-migration-whitepapers.html

Good luck
0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33585454
absolutely wonderful support!

thank you very much to all the experts, esp. AmmarR!
0
 
LVL 2

Author Closing Comment

by:RakeshBhandari
ID: 33585461
thank you!
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33585464
your most welcome
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question