We help IT Professionals succeed at work.

Connecting to Oracle with DTS

BeerGod
BeerGod asked
on
Hi-

I wanted to connect to an Oracle db thru DTS and import some data into my SQL 2000 db.

I see the oracle OLEDB and ODBC drivers in the selection list, however, it says that I have to have the Oracle client tools installed. This seems silly to me. Do I really have to install these client tools just to pull some data from an Oracle db thru DTS?

Thanks- BG
Comment
Watch Question

In order to connect to Oracle via odbc, you need a client "driver" installed on the local machine.  This doesn't necessarily mean that you need the entire client tools collection installed, but you will need some sort of ODBC driver to interface with Oracle.  SQL 2k has an Oracle connection dts object that you can use to get around this.  Keep in mind that if you are installing the ODBC driver, it needs to be on the server from which the package executes, not the local machine.

Author

Commented:
thanks for answering. when i am in dts and selecting a driver, i select the oracle odbc. im pretty sure this is part of mdac 2.6. at this point dts tells me i need the oracle client tools installed.

i have searched up and down oracle's site. do you have any clue where i can find this client driver?

thanks - bg

Author

Commented:
Oracle ODBC drivers for Oracle 8.1.7
  NOTE: The Oracle8 and Oracle8i ODBC Drivers are Version 3.51, Level 2 compatible drivers.

http://technet.oracle.com/software/tech/windows/odbc/content.html

from this page i downloaded the drivers, but im not even sure what to do with this stuff. there is no installer just a bunch of files.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
The problem for Oracle (even ODBC) is that the client installation requires many things, even the most basic client installation will put 100+ files on the system.
This is the way Oracle has designed things, and most of the files implement the settings for the connection, as well as some abstraction and translation layer: date, currency and numerical values can be different on the client than on the server, and also the error messages can be produced in the local language instead of the language of the server.

CHeers

Author

Commented:
i installed the client tools = (

Explore More ContentExplore courses, solutions, and other research materials related to this topic.