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

Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25
JJ_R
  • 25
  • 1
  • 1
  • 7
  • 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? – DEEPAK LAKHOTIA Apr 23 '18 at 07: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. – JJ_R Apr 23 '18 at 08: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. – JJ_R Apr 23 '18 at 08:54
  • Yes, you should. Let me add that as an answer. Please mark that as an answer. – DEEPAK LAKHOTIA Apr 23 '18 at 08: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 – Peter Sep 25 '20 at 14:05

3 Answers3

2

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

DEEPAK LAKHOTIA
  • 993
  • 5
  • 10
  • 1
    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". – JJ_R Apr 23 '18 at 09:12
  • Do not use Microsoft OleDB Provider for Oracle, it is deprecated: https://learn.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-oracle?view=sql-server-2017 – DEEPAK LAKHOTIA Apr 23 '18 at 09:21
1
  • Right click Project and click Properties
  • In Configuration Properties at left side click Debugging
  • set Run64BitRunTime options as false

screenshot

wibeasley
  • 5,000
  • 3
  • 34
  • 62
emopi
  • 11
  • 2
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.

renaissanceMan
  • 385
  • 2
  • 7