SSIS: How to know if the installed Oracle Client is 32bit or 64bit on your SSIS machine


In your SSIS packages, if you need to read from or write to Oracle likely that you most probably hit some Oracle Client related configuration issues. ORACLE_HOME, PATH environmental variables etc.

Sometimes you may see a machine needs to run SSIS packages and you see that an Oracle Client is installed but you don’t know if the Oracle Client is 32bit or 64bit; and you want to know if it is 32bit or 64bit for some reason (Maybe a “Connection Manager” using Oracle OLEDB Provider is not passing “Test connection” in BIDS or this works but the package fails when you tried to run in SQL Job). Needles to say that all thos problems is a topic if your OS is 64bit.

The fastest way to understand if an installed Oracle Client is 64bit or 32bit, too look for “lib32” folder under ORACLE_HOME. If the Oracle Client is 32bit, it will contain a “lib” folder; but if it is a 64bit Oracle Client it will have both “lib” and “lib32” folders.

Hope it helps you sometime, someday 🙂

Comments (2)

  1. Syed Anwar-TechM says:

    This is an expected behavior. Starting from Oracle database 11gR2, 32-bit libraries are not being shipped with the 64-bit Oracle database server or 64-bit Oracle database client media. Hence after installing Oracle database 11gR2, you will not find lib32 folder inside $ORACLE_HOME.

    If you want 32-bit libraries, you need to install 32-bit client which is shipped as a separate media. This  should be installed only on a new Oracle home. You should neither install it on 64-bit server home nor on 64-bit client home.

    For Linux x86-64, the client shiphome for Linux x86 can be used because that essentially is the 32-bit equivalent.

    Thanks

    syed

    8080627888

  2. sri says:

    can you identify the problem in the fallowing scenario

    i am looking for solution for the scenario.

    scenario is:

    i have to load the data from oracle to sql server tables through ssis.

    the loading must be haappened incrementally for this i have to take last_updated_date from my destination table

    and have to pass to OLEDB source for filter data .

    the data must be filtered at source it self as the oracle tables have lakhs of records, but wana bring only records from last_updat_date.

    i have set Run64bitrun as false in ssis solution properties

    always usedefaultcode page=true at OLEDB source

    i tried fallowing methods but all are failed.

    1)excuteSQL task—taken last_updated_date from query and result set is passed as single row to user:Udate_key

    execute sql task—->dataflow(OLEDBsource–>OLEDB Destination)

    when i tried the fallowing select * from sourcetable where last_updated_date>? " the oledb command throws fallowing error

    ————

    Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)

    2)when i tried with taking another package level variable USER::SqlQuery as evaluate asexpression =true

    and entered "SELECT * FROM INVOICES_ALL WHERE LAST_UPDATE_DATE >" +@[User::UdateKey]

    the expression got validated

    but again oledb source with "sql command from variable" option throws error

    —-

    Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

    An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00936: missing expression

    ".

    3)

    execute sql task——->script task—data floe task(oledb source—-oledb destination)

    by taking user::udatekey as readonly variable and user::sqlquery as read and write variable for script task to dynamically assigning the value to variable

    Dts.Variables("SqlQuery").Value = "SELECT * FROM AP_INVOICES_ALL WHERE LAST_UPDATE_DATE >" + Dts.Variables("Updatekey").Value

    Dts.TaskResult = ScriptResults.Success

    it also throwing error.

    —————————————————————

    SELECT * FROM TRNG.AP_INVOICES_ALL                        works fine

    but

    the fallowing queries parsed successfully

    SELECT * FROM AP_INVOICES_ALL WHERE  LAST_UPDATE_DATE ='5/21/2001'

    SELECT * FROM AP_INVOICES_ALL WHERE  LAST_UPDATE_DATE >'5/21/2001'

    but when trying to preview or press ok

    There was an error displaying the preview. (Microsoft Visual Studio)

    ===================================

    ORA-01843: not a valid month (Microsoft OLE DB Provider for Oracle)

    ——————————

    Program Location:

      at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)

      at System.Data.OleDb.OleDbDataReader.GetRowHandles()

      at System.Data.OleDb.OleDbDataReader.ReadRowset()

      at System.Data.OleDb.OleDbDataReader.Read()

      at System.Data.OleDb.OleDbDataReader.HasRowsRead()

      at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

      at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

      at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)

      at System.Data.Common.DbCommand.ExecuteReader()

      at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, DbConnection connection, Control parentWindow, IServicePro