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