DTS to import data from Oracle tables

Posted on 2005-03-15
Medium Priority
Last Modified: 2013-11-30
Hi all, does anybody could give me indications on how to do to build a DTS that imports data from a Oracle db to sql-server. The tables have the same schema.

Indications + code are welcome

Question by:NetDeveloper
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
  • 3
  • 2

Expert Comment

ID: 13549910
Create a DTS package. Drop a OLEDB for Oracle connection and a OLEDB for SQL connection on to the package. Click and select both connections and use the Transform data task to connect the Oracle OLEDB connection to SQL OleDB Connection. Right click on the Transform Data task and select the oracle table as source, next select the sql table as the destination. If both sql and oracle tables are the same, have the same names then the fields will be mapped automagically. If not the you will have to manual map the oracle fields to the sql fields. Then run the package.

see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dts_overview.asp for over view of DTS
OR http://www.sqldts.com/default.aspx?278,4
LVL 10

Author Comment

ID: 13550044
Hi, where I find  OLEDB for Oracle connection  and OLEDB for SQL connection

and Transform data task

does I have to drop it too ?

LVL 10

Author Comment

ID: 13550049

so for data replication ... as this case there is no need to write a custon Active X

Accepted Solution

JardIT earned 2000 total points
ID: 13550087
Create a new package by right clicking on the data transformation services folder and select NEW Package. When the package opens you should see two tool boxes in the top left hand corner of the screen. One titled Connections and one titled tasks. In connections you can use odbc for oracle the icon that is comprised of 2 databases or select the icon with the earth on it. In the opening window under connections drop down you should be able to choose oracle oledb . Then follow the steps entering in your connection sting. sql oledb Connection is the icon that looks like a server.

Then the transform data task is in the tasks tool box and looks like a cloud with an arrow. Select the oracle and sql connections and the select the transform data task.

Expert Comment

ID: 13550098
There should be no need to write Active X script if the schemas are the same

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 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