SSIS - Oracle to SQL Numeric

Recently ran into an issue where we use SSIS to pull data from Oracle and issues with converting a Oracle Numeric data type to SQL Server. This did not go well, final corrections seemed ok, until we deployed them.

There are several solutions to this problem, it seems that having the right drivers of course helps, and so does casting / converting the data while brining it in from Oracle.

I found a great blog entry on this, i've copied it below (as great links often disappear), and also the link:

SSIS 64bit – Using Oracle Provider

Hello everyone, running an Oracle Provider over a 64 bit system could be a pain in the…
The purpose of this document is to explain the main issues running an SSIS solution using Oracle as a data source under 64bit. This document would explain the problems. Offer solution and work around procedures.

Symptoms:
When dealing with Oracle as a data-source we face two known issues:
1.            The case of the Numeric Type Casting:
                Oracle uses a data –type named numeric for numerical data. the numeric data
                type  usually requires   setting precision format e.g. numeric(18,2).
                While the OLEDB Provider can handle the numeric type. It does so only when precision is set.when
                facing a meta-data of type numeric where precision wasn't set. an exception is thrown requires us
                to use casting within the Oracle query. This is not to terrible if you have single table/query. But try
                to imagine maintaining hundreds of queries ?

2.            Provider Data rate:
                The purpose of 64bit hardware is to accelerate the performance.
                Using a 32 bit provider on a 64bit hardware limit the performance of the package. (OLEDB
                Provider uses COM objects and x86 type system).

Solution Scenario:

The Following solution would handle both issues. The proposed solution is to download and setup a 64 bit generic provider. The only question is which provider and what scenario ?

Prepare Step: Remove Previous Oracle Client and or Installations

1. Using Oracle Install to remove all Oracle objects (including ROOT)

2. Remove specific Oracle Assemblies from GAC:
Look under %WindowsDir% for the Assembly folder and remove all Oracle specific Assemblies (Mainly Oracle Policy and Oracle Data Provider).
beware not to remove Microsoft generic assemblies (System.* or Policy..Microsoft)

3. Remove Oracle Registry Entries4. Delete Oracle Folder (remaining items).

Phase 1: Upgrading to 10g (64bit) 10.2.0.3 - Provider
The following phase would increase your provider throughput and allow you to execute a 64bit calls to the Oracle Hardware, However it wouldn't solve your Numeric casting issue.
This step is supported by two 64bit Hardware schema:

1.            For x64 base Hardware:
                Download and Install the specific provider for x64.
                http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

2.            For IA64 based Hardware (Both IA64 and Itanium 2):
                Download and install the specific Itanium provider.
                http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

Remark: Keep in mind that specific Itanium Processors, such as Montecito requires additional patch offered on the same link!.

Phase 2: Establish an Oracle Connection
1. Use your Oracle DBA to establish your TNS_ADMIN Settings and set your "tnsnames.ora" file.
2. Use the Registry to set a TNS_LANG with equivalent settings as Oracle Server.

Phase 3: Upgrading to Oracle Data Access Components (ODAC) 11g - 11.1.0.6.21
This phase would solve the need to cast the SQL query to support the numeric data-type. By using the Oracle Managed Provider for .NET. a Package can be developed without the need to cast the numeric field. Performance Test shows and estimated throughput of 1Milion rows per minute.

Important Remarks:
1.            Do not remove previous version of 10.2.0.3 provider!
2.            This phase only applies to x64 Type System. No Itanium Support at this time!!
                Itanium users, would have to solve the numeric casting problem using casting at            the sql origin.

Users of the x64 hardware, please download and install the following component:
Oracle 11g ODAC and Oracle Developer Tools for Visual Studio

http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html

Phase 4: Establish an Oracle Connection
Since the 11g ODAC usually setup an additional Oracle Root , we need to establish additional Oracle Setting for the additional Oracle 11g Root.
1. Use your Oracle DBA to establish your TNS_ADMIN Settings and set your "tnsnames.ora" file.
2. Use the Registry to set a TNS_LANG with equivalent settings as Oracle Server.

3. The Registry Setting for the 11g provider , would appear under the WOW64 entry within the registry.

Which Provider To Choose:

Using the SSIS designer within visual studio, be sure to choose the following provider:

Oracle Data Provider for .NETPlease do not be mistaken with the Oracle Client Provider for .NET which is a Microsoft generic provider!!!!!Q. I can't see the Oracle Data Provider For .NET within the List ?A. Please do the following:

Download the Oracle 11g ODAC and Oracle Developer Tools for Visual Studio for x86 (32 bit) and
extract the assemblies files.

Using the GAC utility to register assemblies
Go to machine.config of the 64bit which could be located at:
%%WindowsDir%\Microsoft.NET\Framework\x64\v2.0.50727\Config
And look for the Oracle Data Provider which is located in the "<system.data>" section.
Copy the settings (alter to match the public key of the x86 assemblies) to the machine.config file of
the x86 settings.
restart the server

Do not forget to execute SSIS package using either SQL Server Job (Execute SSIS package step), or by using the dtexec command line.

Have fun,

Eran

Rob Orchiston said:

Hi Eran, I just stumbled across your post here while looking for something unrelated on google. I spent several hours trying to get thru to a microsoft technical representative in Nth America to get some help with 64 bit providers on Oracle. The MS call routers gave me the run around and I never actually talked with an engineer, although my MSDN subscription allows for several free support calls.

As for the 64 bit Oracle Providers on our 64 bit Windows Server we couldn't get the MS or the Oracle provider for Oracle working and on Friday ended up conceeding so today the Server is being rebuilt as a 32 bit Windows Server. Our test environment is running 32 bit and SSIS packages work just fine there. I had spent several days trying to get the 64 bit providers to work but we have a production deadline and a hosted server which made resolving the issue more problematic.

# October 20, 2008 4:39 PM

Eran Sagi said:

Hi Rob,

I would like to help; could you give me more details?

The Scenario in the Blog is the result of multiple solutions all are working!

From your comments it looks like you main problem was detecting/using the providers! Here are some thoughts on that issue:

1. Did you manage to use tnsping to test if the

oracle server is connected ? (You can also check

Using PL/SQL or Toad)

2. Most of the 64bit providers, would work only in

Genuine 64bit environment, meaning, you have to

Use either the dtexec command line utility or the

SQL Server Job (Under 64bit Instance).

3. Using Visual Studio and/or DTExecUI would only

Activate the 32bit providers. Those providers

Doesn't include the Oracle Data Provider for .NET,

Unless specifically installed both 32bit and x64

Version!

4. Did you manage to perform "Test Connection" using

the Data source Windows of the SSIS ?

Keep in mind the running a 32bit SSIS solution would limit you to a 3GB memory space (when using the /3GB settings of the system). and limited support for multi threaded. the performance gaps are usually high!

It is recommended to try harder to achieve 64bit environment.

Since you have contacted your Microsoft Local, try to relay the Call to MCS Israel, maybe we can help here!

Regards,

# October 21, 2008 4:15 PM

Mark Frawley said:

Hello Eran

I am having this exact problem. Before I embark on the extensive things you recommended, I have a few questions I hope you can answer:

1 - just before "Phase 4..." you say to download and install "Oracle 11g ODAC and Oracle Developer Tools for Visual Studio" but don't say anything about 32 vs 64 bit. Later, in answering what to do if the "Oracle Data Provider For .NET" does not appear in the list, you specifically say to install the 32 bit version. Is the overall conclusion that both are to be installed ?

2 - in your response to Rob, #2, where you say most 64 bit providers will only work in a genuine 64 bit environment and mention using dtexec or an Agent job, are you suggesting that you cannot execute packages with a 64 bit Oracle provider from within BIDS ?

Thanks for a very helpful post...

Mark

# November 17, 2008 2:36 PM

Hi Mark,

Well you understood corectly:

a. You should install Both the 32bit and the 64bit

versions. (require geniune x64 environment).

b. 64bit SSIS Environemt is enabled during the

execution of dtexec and SQL Server Agent job

(64 bit version of SQL). Running the package

from within the BIDS would result launching a

32 bit environment.

I hope that helps,