asked
Connecting to Oracle via SSIS
I need to get some data out of an Oracle database and bring it into SQL Server. On my local box I created the linked server to test it which works fine and can run the query to get the data. I can connect to the Oracle database via a test udf document, but I can't connect via SSIS. Using the same connection details as my linked server I'm getting the following message: *Test connection failed because of an error in initialising provider. No error message available, result code: E_UNEXPECTED(0x8000FFFF)* Has anyone got any ideas what I'm missing please.
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Do you have orca file...Check our orca file whether it has correct credentials
I have the TNSNames.ora file, is the the orca file something specific for SSIS?
Yes you are right. ...check out the credentials here....what is the protection level of your package please?
Thank you for your help. It's currently set to Encrypt with User Key, I've also tried Don't save sensitive. This is a completely new package that I am using to make sure I can get the connection working, there is nothing in it at all.
Sorry you mean to say that credentials are correct? Where is the ora file stored please ...is it under something like c/oracleclient?
The tnsnames.ora file is saved in c:\program files (x86)\Oracle and I've tested the credentials via a udf file and it seems to be fine.
Hi Here is a snippet of tsnnames file that we here are using to establish connection with the oracle server:- it is stored under path - C:\oracle_client\product\11.2.0\client_1\network\admin MARRYV.BTYP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = pizarro.btyp)(Port = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = martv.yell) ) ) 1. we are using oledb source and oracle provider for oledb 2. MARRYV is our server name or file name 3. username is analysis_services and some password (you may need to check this as in oracle set up we have different usernames and passwords and you can access to certain objects using these credentials) Try running Visual Studio as the admin
Yes the credentials are correct and the tnsnames.ora file is in c:\program files (x86)\oracle
Hi the credentials are correct I've confirmed and the work when testing with a udl file. The tnsnames is stored here C:\app\xxx\product\11.2.0\client_1 where xxx is my username.
Sorry, that was all I could suggest. You may have to take the help of some seasoned database. Do you have any oracle interface to access the oracle data like sql developer or toad? You might could use that to find out the username, password and hostname for oracle data source. If you happened to resolve it somehow, please do post here as to what did you do for a solution. Thanks
I have seen this before when using a 32-bit Oracle driver and attempting to run 64-bit SSIS or vice versa. What version of SSIS are you using? Is the error happening in Visual Studio development or when running via dtexec?
It's in SSIS, I'm in the process of creating a new package and am unfamiliar with working with Oracle. I started a completely new package and just trying to get the connection before I start doing anything with it. I have set the Run64bitRuntime to false. I thought I'd installed the drivers for Oracle for 64 bit.
19 People are following this question.
