SSIS 64bit – Using Oracle Provider

18 באוקטובר 2008

תגיות: , , , , , ,
7 תגובות

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:

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

  2. Using the GAC utility to register assemblies

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

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

 


 

 

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

7 תגובות

  1. Rob Orchiston20 באוקטובר 2008 ב 16:39

    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.

    הגב
  2. Eran Sagi21 באוקטובר 2008 ב 16:15

    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,
    Eran

    הגב
  3. Mark Frawley17 בנובמבר 2008 ב 14:36

    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

    הגב
  4. Eran Sagi18 בנובמבר 2008 ב 0:53

    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,

    Eran

    הגב
  5. Johnny Lim18 בנובמבר 2008 ב 21:07

    Eran,

    Yr reply to Mark on "install both 32-bit and 64-bit version of 11g ODAC.

    Where can i get the 64-bits 11g ODAC?

    I search in Oracle but no where to be found.

    Pls help.

    Johnny.

    הגב
  6. Edo30 בדצמבר 2009 ב 20:32

    Muchas gracias por tu ayuda, segui al pie de la letra las instrucciones y pude conectarme sin problemas.

    הגב