Solved

using ssis to copy oracle table to sql server table

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 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

13 Experts available now in Live!

Get 1:1 Help Now