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?
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.
