Oracle's OLEDB Provider VS Microsoft OLEDB Provider for Oracle in SSIS

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 5, 2012 at 8:46 am

  #1509157

  To get the download to work correctly I had to Click Open as opposed to Save and then Save the files.:w00t:

  Installation in progress... Now comes the fun part.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 5, 2012 at 8:54 am

  #1509162

  I run the install and I get this error.

  I don't remember how to correct the problem?

  Environment variable: "PATH" - This test checks whether the length of the environment variable "PATH" does not exceed the recommended length.

  Expected Value

   : 1023

  Actual Value

   : 1153

   

  Edited: Disregard, I did not realize that the error was the Windows Environmental Variable Path.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• SQL Surfer '66

  SSCertifiable

  Points: 5161

  More actions

  July 6, 2012 at 12:39 am

  #1509493

  I really can't understand why Oracle still uses search paths for the client software :crazy:

  It feels so 80's (DOS where the days).

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 6, 2012 at 5:49 am

  #1509620

  I', not going to mess with the PATH Variable for now. I installed the Oracle Client 32 & 64 bit.

  I get an error when testing an Oracle connection.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 6, 2012 at 5:53 am

  #1509622

  Since I have not edited the sqlnet.ora since installation I suspect that could be the problem.

  Listed below is what is in both files:

  TNSNames.Ora

  XXXXXX_RDB_QA=

  (DESCRIPTION =

  (ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.CC)(PORT = 1521))

  )

  (CONNECT_DATA =

  (SERVICE_NAME = XXXXXXqa)

  )

  )

  SQLNet.ora

  # sqlnet.ora Network Configuration File: D:\Oracle\product\11.2.0\client_2etwork\admin\sqlnet.ora

  # Generated by Oracle configuration tools.

  # This file is actually generated by netca. But if customers choose to

  # install "Software Only", this file wont exist and without the native

  # authentication, they will not be able to connect to the database on NT.

  SQLNET.AUTHENTICATION_SERVICES= (NTS)

  NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

  Any ideas?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• SQL Surfer '66

  SSCertifiable

  Points: 5161

  More actions

  July 6, 2012 at 6:31 am

  #1509645

  Welsh Corgi (7/6/2012)

  ---

  I', not going to mess with the PATH Variable for now. I installed the Oracle Client 32 & 64 bit.

  I get an error when testing an Oracle connection.

  Unfortunately you have to add the BIN-folders in the Oracle client (32- AND 64-bit) to PATH. Based on your previous post it should look like:

  D:\Oracle\product\11.2.0\client_xxxxx\bin

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 6, 2012 at 6:52 am

  #1509657

  Jan.Sundbye (7/6/2012)

  ---

  Welsh Corgi (7/6/2012)

  ---

  I', not going to mess with the PATH Variable for now. I installed the Oracle Client 32 & 64 bit.

  I get an error when testing an Oracle connection.

  Unfortunately you have to add the BIN-folders in the Oracle client (32- AND 64-bit) to PATH. Based on your previous post it should look like:

  D:\Oracle\product\11.2.0\client_xxxxx\bin

  So then you are telling me that that is why that could be contributing to the following errors?

  When I attempt to login to SQL Plus I get the following error:

  ORA12560: TNS: Proptocol Adapter Error

  I create a connection in SSIS using the Oracle OLE DB Provider and I get the folllowing error when testing the connection:

  ORA-12154 Error when attempting to configure Oracle OLE DB Connection

  Thanks!

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 6, 2012 at 6:55 am

  #1509661

  I see that bot bin folders are in the Path Envionmental Variable.

  I also noticed that the ORACLE_HOME Variable does not have a value?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• prvmine

  SSCertifiable

  Points: 7006

  More actions

  July 6, 2012 at 6:43 pm

  #1509934

  I found Jorg Klein blog very helpful

  http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 6, 2012 at 9:42 pm

  #1509937

  Welsh Corgi (7/5/2012)

  ---

  I found an article that confirms that the 64 and 32 bit Oracle Client must be installed.

  http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

  Yes I found the Jorg Klein Article very helpful but the issues that I'm experiencing are beyond thope of that document.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 6, 2012 at 9:51 pm

  #1509938

  I install two client installations but what happens is one install writes over the other.

  I get one Oracle Home, one registry entry, etc. I read that you need to start the setp in the command line or this will happen. I tried that out but I got lost.

  It is not like Oracle 10g or below, very confusing as an OCP 9i and 10g

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• prvmine

  SSCertifiable

  Points: 7006

  More actions

  July 6, 2012 at 10:13 pm

  #1509939

  1st Install Oracle 11g R1 32 bit – Custom

  change Name to end with home2

  Change path to end with client_32

  2nd Install Oracle 11g R2 64 bit – Custom

  By default 11gR2 uses home1 – there is no option to change this

  Change software locations to end with client_64

  Rob Kerr has a white paper you can down load

  http://www.robkerr.com/2010/03/oracle-driver-configuration-for-ssis.html

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 7, 2012 at 12:02 am

  #1509942

  prvmine (7/6/2012)

  ---

  1st Install Oracle 11g R1 32 bit – Custom

  change Name to end with home2

  Change path to end with client_32

  2nd Install Oracle 11g R2 64 bit – Custom

  By default 11gR2 uses home1 – there is no option to change this

  Change software locations to end with client_64

  Rob Kerr has a white paper you can down load

  http://www.robkerr.com/2010/03/oracle-driver-configuration-for-ssis.html

  Thanks for the information.

  If you install the 32 bit 1st you get an error when you insall the 64 bit that your machine is 32 bit.

  You have to install the 64 bit 1st because this is a bug with 11g install.

  .

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• prvmine

  SSCertifiable

  Points: 7006

  More actions

  July 7, 2012 at 12:19 am

  #1509944

  the error is expected when installing the 64 bit client second - just click continue

  I have step by step screen shots I can send via email

  - it took me six tries to get it working

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 7, 2012 at 12:30 am

  #1509946

  prvmine (7/7/2012)

  ---

  the error is expected when installing the 64 bit client second - just click continue

  I have step by step screen shots I can send via email

  - it took me six tries to get it working

  Well thank you.

  The article looks very good.

  I have not tried it six times yet but I was starting to doubt myself.

  But now thanks to your help I feel better.

  It is 2:28 AM in my time zone, and I worked close to 50 hours but I will try it tomorrow.

  Thanks again, I appreciate it very much and have a good day.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/