Visual studio 2017 data tool SSIS with Oracle OLEDB data source

Question

Visual studio 2017 data tool SSIS with Oracle OLEDB data source

sqlfriend

SSC Guru

Points: 52769
More actions
March 9, 2018 at 11:07 am

#327801

We recent upgraded our server from 2014 to SQL 2017. And we use visual studio 2017 data tools for SSIS, SSRS.
I use Oracle provider for OLEDB driver.
Our oracle is 12c , and the client I downloaded is ODAC 121010.
These all work in our SQL server 2014 environment.
But not working on the environment of 2017 SSIS.
For SSIS, I found if we have an oracle OLEDB source in SSIS, the connection is OK, but when I click the view existing data in the screen of OLEDB source , it shows error:
OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
I tried in another server, it shows a different error:
The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)
It seems the DataTool using Visual studio 2017 is not working somehow with the driver.
But I don't know it is a problem with the driver or it is visual studio data tools.
Any one has the experience and how to fix this?
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

At least be sure you match the 32-bit vs. 64-bit execution context for the SSIS package to match the "bit-ness" of the ODBC driver. If the driver is 32-bit, then the package MUST execute as 32-bit. It's possible that this may have changed in some way.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

I installed both 32 bits ODAC and 64 bits.
I run it from Visual studio 2017 data tool, so it is using 32 bits. We are using Oracle OLEDB data source not ODBC.
Nothing changed except the visual studio version.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

sqlfriends - Friday, March 9, 2018 11:43 AM
I installed both 32 bits ODAC and 64 bits.
I run it from Visual studio 2017 data tool, so it is using 32 bits. We are using Oracle OLEDB data source not ODBC.
Nothing changed except the visual studio version.

Maybe that would be worth a try - change to the ODBC driver. Just be sure that if you install both 32-bit and 64-bit, that you create differently named ODBC connections among the two drivers. Not sure it's a good solution, but it might work....

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

No. thanks, we have hundreds of SSIS packages using Oracle OLEDB driver. which is supposed to work better than ODBC.
32 bits is for Visual studio, 64 bits is for Server jobs.

subramaniam.chandrasekar SSCarpal Tunnel Points: 4223 More actions · Answer 5

sqlfriends - Friday, March 9, 2018 11:07 AM
We recent upgraded our server from 2014 to SQL 2017. And we use visual studio 2017 data tools for SSIS, SSRS.
I use Oracle provider for OLEDB driver.
Our oracle is 12c , and the client I downloaded is ODAC 121010.
These all work in our SQL server 2014 environment.
But not working on the environment of 2017 SSIS.
For SSIS, I found if we have an oracle OLEDB source in SSIS, the connection is OK, but when I click the view existing data in the screen of OLEDB source , it shows error:
OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
I tried in another server, it shows a different error:
The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)
It seems the DataTool using Visual studio 2017 is not working somehow with the driver.
But I don't know it is a problem with the driver or it is visual studio data tools.
Any one has the experience and how to fix this?
Thanks

I think the issue might've occurred with SSDT, Source OLEDB connection type. What kind of login are you using against your database?

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

We don't have this issue with SQL server 2014 with OLEDB oracle source or destination.
Login is using the oracle login.

subramaniam.chandrasekar SSCarpal Tunnel Points: 4223 More actions · Answer 7

sqlfriends - Tuesday, March 13, 2018 9:46 AM
We don't have this issue with SQL server 2014 with OLEDB oracle source or destination.
Login is using the oracle login.

If you have a linked server setup between your server to oracle server, then you can use Openquery to test the connection. We'd done the same similar to this earlier.

kevaburg SSCoach Points: 18131 More actions · Answer 8

I am hoping this isn't too obvious but are you using a TNSNAMES.ORA? If so are there any errors in the file that could cause connection errors?

I have had trouble using ODAC12 as well and had to regress back to using the Instant Client and TNSNAMES.ORA to get it working again. It isn't an elegant solution but it did at least work and performance wasn't adversely affected. I had to use this solution not only for SQL Server 2017 (in my test environment) but also 2016.

Even now I am not sure what was going wrong.....

lesdraper Grasshopper Points: 22 More actions · Answer 9

subramaniam.chandrasekar - Tuesday, March 13, 2018 1:57 AM
I am having the same issue as well with SSIS 2017 Professional and the latest Oracle OLEDB divers on a brand new installation. I am able to send data through this connection but there is one thing that remains outstanding on the Visual Studio installation of the Oracle drivers that I did NOT perform on the Oracle database which I suspect could be a potential problem (but remains untested). On the last page of the Oracle driver installation script there was an instruction my DBA did not do. I will post that instruction here. If anyone has performed this step and it is working propely for you, that may be the sulution... just a guess.
To complete the ASP.NET provider setup, run the SQL scripts in c:\program Files (x86)\Oracle Developer Tools for VS2017\ASP.NET\SQL directory on the providers' target database server.
We did not do that (yet). Perhaps this is the problem. Did any of you do this additional step that have this problem?

harsharac Newbie Points: 9 More actions · Answer 10

Hey,

Were you able to find a solution for this issue. I am facing the same issue. Please help.
Thanks.

mr13dp13 Grasshopper Points: 24 More actions · Answer 11

Hello,
Did anyone find a solution to this problem - I am experiencing the same issue - the SSIS OLE DB Connection Successfully tests but in using a OLE DB Source Editor--> on Preview I am getting the same error as you all did:
Provider cannot derive parameter information and SetParameterInfo has not been called. (OraOLEDB)
Thank you so much in advance for any input/feedback

sqlfriend SSC Guru Points: 52769 More actions · Answer 12

I changed to use ADO.net connection instead of OLEDB connection.

There is no error when previewing the data.

However there are pros and cons using ADO connection comparing with OLEDB connection.
So hopefully that bug can be fixed.

mr13dp13 Grasshopper Points: 24 More actions · Answer 13

Unfortunately changing the connection type is not an option in my organization - thank you for the suggestion though

lesdraper Grasshopper Points: 22 More actions · Answer 14

No, sorry I have not found a resolution to this problem. I also discovered that Oracle table names will not work in an OLEDB Command transformation either.