locked
SSIS on x64 Oracle Client x64 RRS feed

  • Question

  • I have tried everything I can find online to resolve an ongoing issue with a SSIS package that pulls data from Oracle.  I have other servers that can function just fine connecting to the same Oracle database.  I have the 10g x64 client installed and I can TNSPing the database from the command prompt.  I get varying errors depending on what I am doing.  If I execute the package stored in SSIS MSDB from SSMS on my computer, it runs just fine.  If I run it from the server I get:

     

    Info: 2008-02-26 23:02:42.59
       Code: 0x4004300A
       Source: Data Flow Task RT_ADDRESS DTS.Pipeline
       Description: Validation phase is beginning.
    End Info
    Progress: 2008-02-26 23:02:42.59
       Source: Data Flow Task RT_ADDRESS
       Validating: 0% complete
    End Progress
    Error: 2008-02-26 23:02:42.65
       Code: 0xC0202009
       Source: ROCKDBA DATA UPDATE (NEW) Connection manager "Rock"
       Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred.
     Error code: 0x80040154.
    An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  H
    result: 0x80040154  Description: "Class not registered".
    End Error
    Error: 2008-02-26 23:02:42.67
       Code: 0xC020801C
       Source: Data Flow Task RT_ADDRESS OLE DB Source [1]
       Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
    ER.  The AcquireConnection method call to the connection manager "Rock" failed w
    ith error code 0xC0202009.  There may be error messages posted before this with
    more information on why the AcquireConnection method call failed.
    End Error
    Error: 2008-02-26 23:02:42.67
       Code: 0xC0047017
       Source: Data Flow Task RT_ADDRESS DTS.Pipeline
       Description: component "OLE DB Source" (1) failed validation and returned err
    or code 0xC020801C.
    End Error
    Progress: 2008-02-26 23:02:42.67
       Source: Data Flow Task RT_ADDRESS
       Validating: 50% complete
    End Progress
    Error: 2008-02-26 23:02:42.68
       Code: 0xC004700C
       Source: Data Flow Task RT_ADDRESS DTS.Pipeline
       Description: One or more component failed validation.
    End Error
    Error: 2008-02-26 23:02:42.68
       Code: 0xC0024107
       Source: Data Flow Task RT_ADDRESS
       Description: There were errors during task validation.
    End Error
    DTExec: The package execution returned DTSER_SUCCESS (0).
    Started:  11:02:35 PM
    Finished: 11:02:42 PM
    Elapsed:  7.156 seconds

     

     

    I tried uninstalling and reinstalling Oracle on this server.  I tried the 32bit Oracle Client as well as the 64bit client.  I rebuilt the package to use Data Flow Tasks from scratch, and I have the same issues.  If I try and build the package on the server itself in BIDS, I get TNS errors in BIDS trying to create connection managers.  I am missing something, but I have no idea what it is.  If I try and use a DataReader Source, I get:

     

    System.InvalidOperationException: Attempt to load Oracle client libraries threw BadImageFormatException.  This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed. ---> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
       at System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp, IntPtr errhp, IntPtr dst_locp, IntPtr src_locp, UInt64 amount, UInt64 dst_offset, UInt64 src_offset)
       at System.Data.OracleClient.OCI.DetermineClientVersion()
       --- End of inner exception stack trace ---
       at System.Data.OracleClient.OCI.DetermineClientVersion()
       at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)
       at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)
       at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OracleClient.OracleConnection.Open()
       at STPSendMailData.BaseData.SetConnectionAndTransaction()

     

     

    It just doesn't add up.  I get this exception with the 64 bit client installed.  Anyone with ideas, I am getting near to the point of migrating backwards to 32 bit SQL on this server to be rid of this problem.

    Wednesday, February 27, 2008 4:12 AM

Answers

  • I got this to work, though it is not the ideal solution in my opinion, and I am definately looking for a better solution if there is one. 

     

    What I did was:

    1. Remove the Oracle 64bit client from the server.
    2. Restart Server.
    3. Delete Oracle Folders that remained.
    4. Install Oracle 32bit client onto Server.
    5. Validated I could tnsping and sqlplus into the Oracle Server
    6. Copy the DTS Folder from d:\Srvapps\Microsoft SQL Server (x86)\90\DTS to the parent folder as D:\Srvapps\DTS.
    7. Then I copied the BIDS out from d:\Program Files (x86)\Microsoft Visual Studio 8\ to the parent folder as d:\Srvapps\Microsoft Visual Studio 8\
    8. Load BIDS from the new location and change the package to 64bit = false and test execution.  It ran fine.
    9. I created a job to CmdExec Step to call the package from the 32bit DTExec.
    10. Test Package (it failed)
    11. Changed SQL Service account to my user account on service
    12. Re-test Package (it worked)
    13. Open new connection to Server and login as SQL Service Account.
    14. Watch server do all the first logon script executions and create the workspace for the user.
    15. Log off SQL Service Account
    16. Change SQL Agent back to the SQL Service Account
    17. Retest Package (it worked)

    So I am tempted to try to go back to the 64bit client and see if the issue wasn't just that the Service Account had never been logged in, so the registry search for the Oracle Client location got nothing.  Any thoughts on this?  I think this is the first time I have ever logged in to any of our SQL Servers with the service account.  I do everything from my account on the server, including installing SQL, securing it, configuring it, etc...  I checked all the other servers, and everyone I have built, the SQL account has never been logged in, so I will work on doing that tomorrow.

     

    Am I failing to follow a best practice by never using this account? 

     

    EDIT:

     

    I should also note that now the Linked Server in SQL Server, won't work, but it did work under the 64bit client.

    Thursday, February 28, 2008 5:13 AM

All replies

  • is sql server 2005 installed on the server that runs the package?  if so, which version is it?
    Wednesday, February 27, 2008 8:07 AM
  • The server is installed as:

     

    Microsoft SQL Server 2005 - 9.00.3152.00 (X64)   Mar  3 2007 05:22:48   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

     

     

     

    Wednesday, February 27, 2008 12:23 PM
  • I got this to work, though it is not the ideal solution in my opinion, and I am definately looking for a better solution if there is one. 

     

    What I did was:

    1. Remove the Oracle 64bit client from the server.
    2. Restart Server.
    3. Delete Oracle Folders that remained.
    4. Install Oracle 32bit client onto Server.
    5. Validated I could tnsping and sqlplus into the Oracle Server
    6. Copy the DTS Folder from d:\Srvapps\Microsoft SQL Server (x86)\90\DTS to the parent folder as D:\Srvapps\DTS.
    7. Then I copied the BIDS out from d:\Program Files (x86)\Microsoft Visual Studio 8\ to the parent folder as d:\Srvapps\Microsoft Visual Studio 8\
    8. Load BIDS from the new location and change the package to 64bit = false and test execution.  It ran fine.
    9. I created a job to CmdExec Step to call the package from the 32bit DTExec.
    10. Test Package (it failed)
    11. Changed SQL Service account to my user account on service
    12. Re-test Package (it worked)
    13. Open new connection to Server and login as SQL Service Account.
    14. Watch server do all the first logon script executions and create the workspace for the user.
    15. Log off SQL Service Account
    16. Change SQL Agent back to the SQL Service Account
    17. Retest Package (it worked)

    So I am tempted to try to go back to the 64bit client and see if the issue wasn't just that the Service Account had never been logged in, so the registry search for the Oracle Client location got nothing.  Any thoughts on this?  I think this is the first time I have ever logged in to any of our SQL Servers with the service account.  I do everything from my account on the server, including installing SQL, securing it, configuring it, etc...  I checked all the other servers, and everyone I have built, the SQL account has never been logged in, so I will work on doing that tomorrow.

     

    Am I failing to follow a best practice by never using this account? 

     

    EDIT:

     

    I should also note that now the Linked Server in SQL Server, won't work, but it did work under the 64bit client.

    Thursday, February 28, 2008 5:13 AM
  • Thursday, February 28, 2008 5:36 AM