question

Mrs_Fatherjack avatar image
Mrs_Fatherjack 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.
ssisoracle
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
1 Like 1 ·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
Do you have orca file...Check our orca file whether it has correct credentials
10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
I have the TNSNames.ora file, is the the orca file something specific for SSIS?
0 Likes 0 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
Sorry, I'm a newbie at working with Oracle
0 Likes 0 ·
erlokeshsharma08 avatar image erlokeshsharma08 commented ·
Yes you are right. ...check out the credentials here....what is the protection level of your package please?
0 Likes 0 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
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.
0 Likes 0 ·
erlokeshsharma08 avatar image erlokeshsharma08 commented ·
Sorry you mean to say that credentials are correct? Where is the ora file stored please ...is it under something like c/oracleclient?
0 Likes 0 ·
Show more comments
Tom Staab avatar image
Tom Staab answered
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?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 avatar image erlokeshsharma08 commented ·
Yes a very important point
0 Likes 0 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.