There was an error trying to establish an Oracle connection with the database server

Hi @Jobin, Priscilla,

Thank you for reaching out to Microsoft Q&A forum!

It seems you’re encountering an issue with establishing an Oracle connection using SSIS Connection Manager in Microsoft Visual Studio. Let’s troubleshoot this together! 😊

64-bit Provider Error: The error message you received, DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR, suggests that the requested OLE DB provider MSDAORA.1 is not registered, possibly due to a lack of a 64-bit provider. Here are some steps to address this:

Install Oracle Client: Ensure that the Oracle client is installed on the server where your SSIS package is deployed. You can use the Oracle Instant Client or the Oracle Provider for OLE DB (OraOLEDB.Oracle). The latter is not included in the Instant Client, so you’ll need to install it separately1.

  **Check Architecture:** Confirm that the architecture (32-bit or 64-bit) matches your application. If you’re using the deprecated MSDAORA provider from Microsoft, it’s only available in 32-bit. [For 64-bit, use the provider from Oracle](https://stackoverflow.com/questions/56657802/oracle-connection-issues-from-ssis)[1](https://stackoverflow.com/questions/56657802/oracle-connection-issues-from-ssis).
  
     **TNS Admin Environment Variable:** If you’re using a tnsnames.ora file, set the **`TNS_Admin`** environment variable to point to the folder containing the tnsnames.ora file. This step is necessary if you haven’t installed an Oracle client. [On Windows, you can add this environment variable through system settings](https://stackoverflow.com/questions/56657802/oracle-connection-issues-from-ssis)[1](https://stackoverflow.com/questions/56657802/oracle-connection-issues-from-ssis).
     
     **Offline Mode:** Sometimes, SSIS may be in “Work Offline” mode accidentally. To check and fix this:
     
        - Go to the SSIS menu option.
        
           - Find the selection “Work Offline.”
           
              - [Unselect it if it’s enabled](https://stackoverflow.com/questions/56657802/oracle-connection-issues-from-ssis)[2](https://stackoverflow.com/questions/32701696/the-connection-manager-will-not-acquire-a-connection-because-the-connection-mana).
              
              **Validate Credentials:** When validating the package, you mentioned an **`ORA-01017`** error (invalid username/password). Double-check that the credentials used during validation match those used when running the package locally.
              

Remember to test the connection after making any changes.

Best regards,