Solved

using ssis to copy oracle table to sql server table

Posted on 2014-09-14
4
4,017 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

786 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