O'Reilly logo

Professional SQL Server™ 2005 Integration Services by Mike Murphy, Haidong Ji, Jason Gerard, Erik Veerman, Andy Leonard, Kathi Kellenberger, Douglas Hinson, Darren Green, Allan Mitchell, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8.3. Oracle

Because of SQL Server's world-class reporting and business intelligence tools, more and more shops running Oracle rely on SQL Server for their reporting needs. Luckily, importing data from Oracle is much like importing from other sources, such as a text file or another SQL Server instance. In this section, you'll learn how to access data from a sample Oracle database.

8.3.1. Client Setup

An Oracle data provider is supplied with SQL Server 2005. The caveat is that the Oracle Client 8-1.7.0 or greater must be installed in order to use the data provider. The Oracle Client can be installed by running the Oracle Universal Installer. See your Oracle administrator for help. Alternately, the free Instant Client can be downloaded from the Oracle Web site at www.oracle.com/technology/tech/oci/instantclient/instantclient.html. If you use the Instant Client, be sure to install the Basic and ODBC packages.

Installing the Instant Client is as simple as copying some files, setting some environment variables, and running a batch file. You are not going to look at the steps here, as Oracle could modify them in the future.

Once the Oracle Client is installed, a tnsnames.ora file must be placed in the Oracle Home directory. The tnsnames.ora file is used for the same purpose as ODBC Data Source connections. It contains all of the information required to locate the Oracle database. An alias for the database is defined. The alias is used later when setting up the connection in SSIS or ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required