Importing From Oracle In SSIS

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 learn how to access data from an Oracle database with the built-in OLE DB provider and the Oracle client.

How To Connect to Oracle Database through SSIS

Oracle Client Setup

Connecting to Oracle in SSIS is a two-step process. First, you install the Oracle client software, and then you use the OLE DB provider in SSIS to connect to Oracle.

To be sure, the Microsoft Data Access Components (MDAC) that come with the operating system include an OLE DB provider for Oracle. This is the 32-bit Microsoft-written OLE DB provider to access an Oracle source system. However, even though the OLE DB provider is installed, you cannot use it until you install a second component, the Oracle client software. In fact, when you install the Oracle client software, Oracle includes an OLE DB provider that can be used to access an Oracle source. The OLE DB providers have subtle differences, which are referenced later in this section.

Installing the Oracle Client Software

To install the Oracle client software, you first need to locate the right download from the Oracle website at WWW.ORACLE.COM. Click Downloads and then click the button to download 12c. Accept the licensing agreement and select your operating system. As you are well aware, there are several versions of Oracle (currently Oracle 11g, 11g Release 2, 12c), and each has a different version of the Oracle client. Some of them are backward compatible, but it is always best to go with the version you are connecting to.

It is best to install the full client software in order to ensure that you have the right components needed for the OLE DB providers.

Configuring the Oracle Client Software

Once you download and install the right client for the version of Oracle you will be connecting to and the right platform of Windows you are running, the final step is configuring it to reference the Oracle servers. You will probably need help from your Oracle DBA or the support team of the Oracle application to configure this.

Related Article: SSIS Tutorial

There are two options: an Oracle name server or manually configuring a TNS file. The TNS file is more common and is found in the Oracle install directory under the network admin folder. This is called the Oracle Home directory. The Oracle client uses the Windows environment variables %Path% and %ORACLE_HOME% to find the location to the client files. Either replace the default TNS file with one provided by an Oracle admin or create a new entry in it to connect to the Oracle server.

A typical TNS entry looks like this:

[Reference name] =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = [Server])(PORT = [Port Number])) )
(CONNECT_DATA =
(SID = [Oracle SID Name])
)
)

Replace the brackets with valid entries. The [Reference Name] will be used in SSIS to connect to the Oracle server through the provider.

64-Bit Considerations

As mentioned earlier, after you install the Oracle client software, you can then use the OLE DB provider for Oracle in SSIS to extract data from an Oracle source or to send data to an Oracle Destination. These procedures are described next. However, if you are working on a 64-bit server, you may need to make some additional configurations.

First, if you want to connect to Oracle with a native 64-bit connection, you have to use the Oracle-written OLE DB provider for Oracle because the Microsoft-written OLE DB driver for Oracle is available only in a 32-bit mode. Be sure you also install the right 64-bit Oracle client (Itanium IA64 or X64) if you want to connect to Oracle in native 64-bit mode. Although it is probably evident to you, it bears mentioning that even though you may have X64 hardware, in order to leverage it in 64-bit mode, the operating system must be installed with the X64 version.

Furthermore, even though you may be working on a 64-bit server, you can still use the 32-bit provider through the 32-bit Windows emulation mode. Review the 64-bit details in the “Excel and Access” section earlier in this chapter for details about how to work with packages in 32-bit mode when you are on a 64-bit machine. You need to use the 32-bit version of DTExec for package execution, and when working in SSDT, you need to change the Run64bitRuntime property of the project to False.

Related Article: SSIS Interview Questions

Importing Oracle Data

In this example, the alias ORCL is used to connect to an Oracle database
named orcl. Your Oracle administrator can provide more information about
how to set up your tnsnames.ora file to point to a test or production database
in your environment. The following tnsnames file entry is being used for the
subsequent steps:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = VPC-XP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = orcl)
))
To extract data from an Oracle server, perform the following steps. These
assume that you have installed the Oracle client and configured a tnsnames
file or an Oracle names server.

1. Create a new Integration Services project using SSDT.

2. Add a Data Flow Task to the design area. On the Data Flow tab, add an OLE DB source. Name the OLE DB source Oracle.

3. In the Connection Managers area, right-click and choose New OLE DB Connection to open the Configure OLE DB Connection Manager dialog.

4. Click New to open the Connection Manager dialog. Select Microsoft OLE DB Provider for Oracle from the list of providers and click OK.

5. Type the alias from your tnsnames.ora file for the Server Name.

6. Type in the user name and password and check Save my password (see Figure 14-16). This example illustrates connecting to the widely available scott sample database schema. The user name is scott; the password is tiger. Verify the credentials with your Oracle administrator. Test the connection to ensure that everything is configured properly. Click OK to accept the configuration.

7. In the custom properties section of the Oracle Component’s property dialog, change the AlwaysUseDefaultCodePage property to True.

8. Open the OLE DB Source Editor by double-clicking the Oracle Source Component. With the Connection Manager tab selected, choose the Connection Manager pointing to the Oracle database.

9. Select Table or view from the Data access mode dropdown. Click the dropdown list under Name of the table or the view to see a list of the available tables. Choose the “Scott”.“Dept” table from the list.

10. Select the Columns tab to see a list of the columns in the table.

11. Click Preview to see sample data from the Oracle table. At this point, you can add a Data Destination Component to import the data into SQL Server or another OLE DB Destination. This is demonstrated several times elsewhere in the chapter, so it isn’t covered again here.

Importing Oracle data is very straightforward, but there are a few things to watch out for. The current Microsoft ODBC driver and Microsoft-written OLE DB provider for Oracle were designed for Oracle 7. At the time of this writing, Oracle 11g is the latest version available. Specific functionality and data types that were implemented after the 7 release will probably not work as expected. See Microsoft’s Knowledge Base article 244661 for more information. If you want to take advantage of newer Oracle features, you should consider using the Oracle-written OLE DB provider for Oracle, which is installed with the Oracle client software.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator