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.
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 ...