13 Connecting to SQL Server and Importing Metadata

Scenario

Your company has data that is stored in SQL Server and you would like to import this into Warehouse Builder. Once you import the data, you can perform data profiling to correct anomalies, and then transform the data according to your requirements by using mappings.

Solution

In Warehouse Builder, you can connect to non-Oracle data sources. Once connected, you can import metadata just as from any Oracle data source.

Case Study

To connect to SQL Server and import metadata, refer to the following sections:

"Creating an ODBC Data Source"
"Configuring the Oracle Database Server"
"Adding the SQL Server as a Source in Warehouse Builder"
What's Next

If you encounter problems implementing this solution, see "Troubleshooting".

Creating an ODBC Data Source

You must create an ODBC data source to connect to the SQL Server. To do this, you must set up a System Data Source Name (DSN):

Select Start, Control Panel, Administrative Tools, Data Sources (ODBC).

This opens the ODBC Data Source Administrator dialog box.
Navigate to the System DSN tab and click Add to open the Create New Data Source dialog box.
Select SQL Server as the driver for which you want to set up the data source.
Click Finish to open the Create A New Data Source to SQL Server Wizard.
In the Name field, specify a name for the data source. For example, sqlsource.
In the Server field, select the server to which you want to connect and click Next.
Specify whether the authentication should be done at the Operating System level or at the server level. Click Next.
Select the database file and click Next.
Accept the default values in the next screen and click Finish.
Test the data source to verify the connection.

Configuring the Oracle Database Server

Next, you must configure Oracle Database to connect to SQL Server. Warehouse Builder can then use this configuration to extract metadata from the SQL Server. There are two steps involved in this:

Creating a Heterogeneous Service Configuration File
Editing the listener.ora file

Creating a Heterogeneous Service Configuration File

You must create the heterogeneous file in the ORACLE_HOME\hs\admin directory. The naming convention for this file should be as follows:

Must begin with init
Must end with the extension .ora
Must not contain space or special characters

For example, you can name the file initsqlserver.ora.

Enter the following in the file:

HS_FDS_CONNECT_INFO = sqlsource
HS_FDS_TRACE_LEVEL = 0

Here, sqlsource is the name of the data source that you specified while creating the ODBC data source.

Editing the listener.ora file

You must add a new SID description in the listener.ora file. This file is stored in the ORACLE_HOME/network/admin directory.

Modify the file as shown:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = sqlserver)
      (ORACLE_HOME = c:\oracle10g\oracle_home)
      (PROGRAM = hsodbc)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle10g\oracle_home)
      (PROGRAM = extproc)
      )
  )

The SID_NAME parameter must contain the name of the configuration file you created in the previous step. However, it must not contain the init prefix. For example, if the configuration file you created in the previous step was initsqlserver.ora, then the value of the SID_NAME parameter should be sqlserver.

ORACLE_HOME must point to the Oracle home location of your database installation.

The value associated with the PROGRAM keyword defines the name of the executable agent, which, in this case, is hsodbc.

Restart the listener service after making these modifications.

Adding the SQL Server as a Source in Warehouse Builder

The final step involves adding an ODBC module in Warehouse Builder, and importing the data from the SQL server into this module.

To add an ODBC source module in Warehouse Builder:

Within a project in the Project Explorer, navigate to the Databases, Non-Oracle node.
Right-click ODBC and select New.
Create a new ODBC module using the Create Module Wizard.
You can provide the connection information for the source location either at the time of creating the module, or while importing data into this module.
In the Edit Location dialog box, make sure that you enter User Name and Password within double quotation marks ("). For example, if the user name is mark, enter "mark".
For Service Name, enter the SID name you provided in the listener.ora file. Also select the schema from which you wish to import the metadata.

To import metadata into the ODBC module:

Right-click the module and select Import.
Import the metadata using the Import Metadata Wizard.

The tables and views available for import depend on the schema you selected when providing the connection information.

What's Next

After you successfully import metadata into Warehouse Builder, you can use the data profiling functionality to check the quality of the data. Or you can skip data profiling and proceed with designing a mapping to extract, transform, and load the data.

Troubleshooting

Some of the errors that you may encounter while providing the connection information are listed here:

Error

ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Generic Connectivity Using ODBC][Microsoft][ODBC Driver Manager] Data source name 
not found and no default driver specified (SQL State: IM002; SQL Code: 0)

ORA-02063: preceding 2 lines from OWB_###

Probable Cause

Creating the DSN from the User DSN tab.

Action

Create the DSN from the System DSN tab.

Error

ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL 
Server]Login failed for user 'SA'. (SQL State: 28000; SQL Code: 18456)

ORA-02063: preceding 2 lines from OWB_###

Probable Cause

The user name and password in the Edit Location dialog box are not enclosed within double quotation marks.

Action

Enter the user name and password within double quotation marks.

Tip:

Make sure that you restart the listener service whenever you make changes to the listener.ora file.