?
Solved

using ssis to copy oracle table to sql server table

Posted on 2014-09-14
4
Medium Priority
?
4,937 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
[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
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
Mandeep Singh earned 1000 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:Praveen Kumar Chandrashekatr
Praveen Kumar Chandrashekatr earned 1000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

800 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