Solved

using ssis to copy oracle table to sql server table

Posted on 2014-09-14
4
3,858 Views
Last Modified: 2016-02-11
I have no experience with Oracle. I need to copy some tables from an Oracle database to an sql server database. I plan to use SSIS to do it. Is there anything special I will need to know to do this?
0
Comment
Question by:qbjgqbjg
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
Mandeep Singh earned 250 total points
ID: 40322473
Hi,

Some basic points are:

1. if oracle and sql machine are not same. install oracle client on sql machine.
2. setup connection setting on tns.ora on sql machine.(do port opening between for communication).
3. then create a ssis package to transfer data.

try this: http://www.mssqltips.com/sqlservertip/2011/export-sql-server-data-to-oracle-using-ssis/

Refrence:
http://stackoverflow.com/questions/10944968/create-a-ssis-package-to-copy-data-from-oracle-to-sql-server
0
 

Author Comment

by:qbjgqbjg
ID: 40323076
Can you explain step 2 a little more.
0
 
LVL 12

Assisted Solution

by:praveencpk
praveencpk earned 250 total points
ID: 40323883
First install oracle client in your MS SQL Server based on the version i.e 32bit /64bit. then setup the connect string using TNSNAMES.ORA which will communicate between SQL Server and database server.

Check these below doc for more info.
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/
0
 

Author Closing Comment

by:qbjgqbjg
ID: 40323996
Thanks for your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now