none
Oracle Connection Issue E_UNEXPECTED error

    Question

  • I am trying to use SSIS to import a table from SQL Server 1012 to Oracle 11g.

    The oracle database is an established one that has been working fine for 5 years and we have professional Oracle and Sql Server DBA managing the databases.

    On my client machine, I have correctly setup the oracle tnsnames.ora and related ODT packages that allows me to talk to oracle db in C#. It has been working and still working.

    I am new to SSIS though but I can create simple package reading excel files and import to SQL Server 2012 without any issues. When I tried to import the same excel file into the oracle database, I got hit with the connection issue.

    The original message was "Test connection failed because of an error in initializing provider. No error message available, result code: E_UNEXPECTED." The error happened when I tried to hit the "Test Connection" to verify if SSIS can talk to oracle.

    What is going on?

    In the connection manager from SSIS, I used Native OLE DB\Oracle Provider for OLE DB,

    in the server or file name: I entered the entry in tnsname.ora,

    User Name is the oracle db schema name and its corresponding password.

    What have I done incorrectly? I have also installed AttunitySSISOracleAdapter for 32 and 64bits. What else I have to try?

    Wednesday, February 12, 2014 7:27 PM

Answers

  • So this is what you should do, if you want both the version to work fine

    a) Set the Oracle_Home path directory to point to the lets say something like D:\oracle

    b) Install both the versions 32/64 bit on the directory by pointing to this location.

    c) Set the TNS_ADMIN to point to the correct tnsnamems.ora path(i Beleive this is the culprit in your env)

    Follow this link to set up TNS_ADMIN the http://kb.tableausoftware.com/articles/knowledgebase/oracle-connection


    Abhinav http://bishtabhinav.wordpress.com/

    Wednesday, February 12, 2014 9:44 PM

All replies

  • The message tells me that the Provider cant be initialized which i would suspect is because you are working on a 64 bit Machine and the Oracle provide installed is also a 64 bit version.

    Now if you have a 64 bit machine the SSIS connection would fail because the BIDS is a 32 bit S/w and requries 32 bit drivers to be installed. so the resolution could be you to install 32 bit Oracle drivers or 32 bit Oracle 11g, and set up the tnsnames.ora accordingaly


    Abhinav http://bishtabhinav.wordpress.com/

    Wednesday, February 12, 2014 7:32 PM
  • Hello,

    Thank you for your reply. I just would like to clarify your explanation: by "Oracle provide installed is also a 64 bit version", did you mean the Oracle database itself or the oracle client installed on my workstation? If it is database, I have no control of what version of oracle to install as our DBA installed what he thinks is good for us. If it is a client, I have both 32 and 64 bit installed with tnsname setup properly.

    If it is the later, how do I tell ssis to use my 32 bit version of oracle client?

    Thanks again,

    Wednesday, February 12, 2014 8:01 PM
  • Ok so if you have both where is the tnsnames.ora pointed to as to have you updated the PATH environment variable or put more simply place the entries of tnsnames.ora for both the 32 bit and 64 bit versions.

    Abhinav http://bishtabhinav.wordpress.com/

    Wednesday, February 12, 2014 8:47 PM
  • Good question. My tnsnames is actually in 32 bit environment. So avoid ambiguity, I have removed 64 bit version and the path is now pointing to 32 bit. But I still get the same error.
    Wednesday, February 12, 2014 9:15 PM
  • So this is what you should do, if you want both the version to work fine

    a) Set the Oracle_Home path directory to point to the lets say something like D:\oracle

    b) Install both the versions 32/64 bit on the directory by pointing to this location.

    c) Set the TNS_ADMIN to point to the correct tnsnamems.ora path(i Beleive this is the culprit in your env)

    Follow this link to set up TNS_ADMIN the http://kb.tableausoftware.com/articles/knowledgebase/oracle-connection


    Abhinav http://bishtabhinav.wordpress.com/

    Wednesday, February 12, 2014 9:44 PM