0

I have been trying to connect to Oracle DB (11g) in SSIS (VS2015) from past few days and tried all possible solutions but still getting error. I am passing query through SSIS variable, no other parameter is passed, it is just test query which need to retrieve two rows.

Case 1: Tried using Oracle Provider for OLEDB, test connection is successful but getting below error while Preview data:

The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)

Case 2: Tried using Microsoft OLEDB Provider for Oracle, test connection is successful but getting below error while Preview data:

Provider cannot derive parameter information and SetParameterInfo has not been called.

I have been struggling to solve this problem, any help would be appreciated. Thanks in Advance.

Edit: After setting Run64bitRuntime to false, I can extract data when using Oracle Provider for OLEDB, but Preview still gives same error.

Regards,

Jazz

Improve this question
5
  • Does the same query work on Oracle? Also, can you set the Run64bitRuntime property based on the bitness of the provider you're using and test again? Commented Apr 23, 2018 at 7:12
  • Yes, the same query is working in sqldeveloper for Oracle. I have set Run64bitRuntime property to false, even true was giving the same error. Commented Apr 23, 2018 at 8:48
  • 1
    But after setting Run64bitRuntime to false I got error while Preview but when I execute the package it is fetching the data. Thanks, hope I will be able to fetch data from Oracle to SQL server now. Commented Apr 23, 2018 at 8:54
  • Yes, you should. Let me add that as an answer. Please mark that as an answer. Commented Apr 23, 2018 at 8:56
  • @JaiJai Have you solved the preview problem? I am facing exactly the same issue, connection works, execution also, but previews still gives me the same error Commented Sep 25, 2020 at 14:05

4 Answers 4

Reset to default
2

Set Run64bitRuntime to false for the package and then it should work.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Can you tell why Preview gives error still, and it is working only for "Oracle Provider for OLEDB" and not for "Microsoft OLEDB Provider for Oracle".
Do not use Microsoft OleDB Provider for Oracle, it is deprecated: learn.microsoft.com/en-us/sql/ado/guide/appendixes/…
1
  • Right click Project and click Properties
  • In Configuration Properties at left side click Debugging
  • set Run64BitRunTime options as false

screenshot

Improve this answer

Comments

0

Some advice on this is all focused on "Preview". Don't throw the baby out with the bath water. With VS 2017 enterprise I got the "cannot find message text ... " message with Preview but was able to put oracle data into ms sql as a job (the gui has a problem, but not the run time job). I did set AlwaysUseDefaultCodePage to true on Component Properties in Advanced Editor on the OLEDB oracle task.

Improve this answer

Comments

0

For me OraOLEDB works fine with SSIS, but designtime is without preview having the same error. My VS development environment has only x32 driver installed, while SSIS IR has only x64.

My wild guess - VS/SSIS preview syntax (SELECT TOP 200 ??) is not compatible with Oracle FETCH FIRST 200 (or ROWNUM <=200).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.