SSIS Package Execution issue using ODBC Oracle Source

Question

SSIS Package Execution issue using ODBC Oracle Source

skaggs.andrew

SSCrazy

Points: 2474
More actions
May 15, 2018 at 11:26 am

#328713

Hi,
I'm having an issue running a package when I have an ODBC Oracle Source. When I manually execute the package in SSIS, it successfully completes, however the data flow that running has not completed. It loads a good amount of the rows, but doesn't load everything. The data flow is very straight forward, source to target. I tried researching into the pooling settings and didn't find any issues. Doesn't seem like its timing out, but for some reason it just stops. Any suggestions as to what the issue could be? Thanks
Another thing I noticed is, when I open the OLE DB Source Editor and I try to preview the source data, I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)
When I go to the connection manager, open add the password, and test the connection, it says test connection succeeded. Not sure if this is relevant or not. I attached an image so you can see what I mean.

Viewing 12 posts - 1 through 11 (of 11 total)

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

This post duplicates a problem previously posted, without referencing any of the previous material. This forum doesn't generally like to have that happen, as it fragments the conversation and puts people in the position of having two places to go to look at the same problem. I don't have the time at the moment to go look up this user's other post that has this problem in it, so maybe someone else can post the link to the other thread, which is where any other replies should go.

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

skaggs.andrew SSCrazy Points: 2474 More actions · Answer 2

sgmunson - Tuesday, May 15, 2018 12:01 PM
This post duplicates a problem previously posted, without referencing any of the previous material. This forum doesn't generally like to have that happen, as it fragments the conversation and puts people in the position of having two places to go to look at the same problem. I don't have the time at the moment to go look up this user's other post that has this problem in it, so maybe someone else can post the link to the other thread, which is where any other replies should go.

I made this post because the previous post changed directions into a new problem. I was afraid because of the other post's title, I wouldn't get the eyes to read this issue that I am having. My original post was dealing with a buffer error, this has to do with the odbc connection.

Here is my original post about the buffer error:

https://www.sqlservercentral.com/Forums/1934690/SSIS-Buffer-Error?PageIndex=1

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

skaggs.andrew - Tuesday, May 15, 2018 11:26 AM
Hi,
I'm having an issue running a package when I have an ODBC Oracle Source. When I manually execute the package in SSIS, it successfully completes, however the data flow that running has not completed. It loads a good amount of the rows, but doesn't load everything. The data flow is very straight forward, source to target. I tried researching into the pooling settings and didn't find any issues. Doesn't seem like its timing out, but for some reason it just stops. Any suggestions as to what the issue could be? Thanks
Another thing I noticed is, when I open the OLE DB Source Editor and I try to preview the source data, I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)
When I go to the connection manager, open add the password, and test the connection, it says test connection succeeded. Not sure if this is relevant or not. I attached an image so you can see what I mean.

Try to query the Oracle DB using open query instead.

skaggs.andrew SSCrazy Points: 2474 More actions · Answer 4

subramaniam.chandrasekar - Wednesday, May 16, 2018 1:00 AM
skaggs.andrew - Tuesday, May 15, 2018 11:26 AM
Hi,
I'm having an issue running a package when I have an ODBC Oracle Source. When I manually execute the package in SSIS, it successfully completes, however the data flow that running has not completed. It loads a good amount of the rows, but doesn't load everything. The data flow is very straight forward, source to target. I tried researching into the pooling settings and didn't find any issues. Doesn't seem like its timing out, but for some reason it just stops. Any suggestions as to what the issue could be? Thanks
Another thing I noticed is, when I open the OLE DB Source Editor and I try to preview the source data, I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)
When I go to the connection manager, open add the password, and test the connection, it says test connection succeeded. Not sure if this is relevant or not. I attached an image so you can see what I mean.
Try to query the Oracle DB using open query instead.

I tried to query the Oracle DB and I got this error message:
ORA-03114: Not Connected to Oracle
I looked into the tnsnames.ora file and I believe I have the correct information. Not sure why the connection manager says its connected when I put in the username password, but it doesn't show me a preview from the OLE DB source. Just errors here

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

skaggs.andrew - Wednesday, May 16, 2018 9:01 AM
subramaniam.chandrasekar - Wednesday, May 16, 2018 1:00 AM
skaggs.andrew - Tuesday, May 15, 2018 11:26 AM
Hi,
I'm having an issue running a package when I have an ODBC Oracle Source. When I manually execute the package in SSIS, it successfully completes, however the data flow that running has not completed. It loads a good amount of the rows, but doesn't load everything. The data flow is very straight forward, source to target. I tried researching into the pooling settings and didn't find any issues. Doesn't seem like its timing out, but for some reason it just stops. Any suggestions as to what the issue could be? Thanks
Another thing I noticed is, when I open the OLE DB Source Editor and I try to preview the source data, I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)
When I go to the connection manager, open add the password, and test the connection, it says test connection succeeded. Not sure if this is relevant or not. I attached an image so you can see what I mean.
Try to query the Oracle DB using open query instead.
I tried to query the Oracle DB and I got this error message:
ORA-03114: Not Connected to Oracle
I looked into the tnsnames.ora file and I believe I have the correct information. Not sure why the connection manager says its connected when I put in the username password, but it doesn't show me a preview from the OLE DB source. Just errors here

This looks like a communication error between the data sources.

skaggs.andrew SSCrazy Points: 2474 More actions · Answer 6

subramaniam.chandrasekar - Wednesday, May 16, 2018 11:02 PM
skaggs.andrew - Wednesday, May 16, 2018 9:01 AM
subramaniam.chandrasekar - Wednesday, May 16, 2018 1:00 AM
skaggs.andrew - Tuesday, May 15, 2018 11:26 AM
Hi,
I'm having an issue running a package when I have an ODBC Oracle Source. When I manually execute the package in SSIS, it successfully completes, however the data flow that running has not completed. It loads a good amount of the rows, but doesn't load everything. The data flow is very straight forward, source to target. I tried researching into the pooling settings and didn't find any issues. Doesn't seem like its timing out, but for some reason it just stops. Any suggestions as to what the issue could be? Thanks
Another thing I noticed is, when I open the OLE DB Source Editor and I try to preview the source data, I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)
When I go to the connection manager, open add the password, and test the connection, it says test connection succeeded. Not sure if this is relevant or not. I attached an image so you can see what I mean.
Try to query the Oracle DB using open query instead.
I tried to query the Oracle DB and I got this error message:
ORA-03114: Not Connected to Oracle
I looked into the tnsnames.ora file and I believe I have the correct information. Not sure why the connection manager says its connected when I put in the username password, but it doesn't show me a preview from the OLE DB source. Just errors here
This looks like a communication error between the data sources.

I'm wondering, do you think this has to do with the listener.ora file? In my current environment, I can make this connection and run my packages no problem, but I'm working on a migration. Could it be that the listener isn't configured for this new environment?

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

skaggs.andrew - Thursday, May 17, 2018 8:09 AM
subramaniam.chandrasekar - Wednesday, May 16, 2018 11:02 PM
skaggs.andrew - Wednesday, May 16, 2018 9:01 AM
subramaniam.chandrasekar - Wednesday, May 16, 2018 1:00 AM
skaggs.andrew - Tuesday, May 15, 2018 11:26 AM
Hi,
I'm having an issue running a package when I have an ODBC Oracle Source. When I manually execute the package in SSIS, it successfully completes, however the data flow that running has not completed. It loads a good amount of the rows, but doesn't load everything. The data flow is very straight forward, source to target. I tried researching into the pooling settings and didn't find any issues. Doesn't seem like its timing out, but for some reason it just stops. Any suggestions as to what the issue could be? Thanks
Another thing I noticed is, when I open the OLE DB Source Editor and I try to preview the source data, I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)
When I go to the connection manager, open add the password, and test the connection, it says test connection succeeded. Not sure if this is relevant or not. I attached an image so you can see what I mean.
Try to query the Oracle DB using open query instead.
I tried to query the Oracle DB and I got this error message:
ORA-03114: Not Connected to Oracle
I looked into the tnsnames.ora file and I believe I have the correct information. Not sure why the connection manager says its connected when I put in the username password, but it doesn't show me a preview from the OLE DB source. Just errors here
This looks like a communication error between the data sources.
I'm wondering, do you think this has to do with the listener.ora file? In my current environment, I can make this connection and run my packages no problem, but I'm working on a migration. Could it be that the listener isn't configured for this new environment?

To be honest, I've never done this before, so couldn't advise that much. But seems your connection wasn't configured well in your new environment...

sgmunson SSC Guru Points: 110639 More actions · Answer 8

skaggs.andrew - Thursday, May 17, 2018 8:09 AM
I'm wondering, do you think this has to do with the listener.ora file? In my current environment, I can make this connection and run my packages no problem, but I'm working on a migration. Could it be that the listener isn't configured for this new environment?

I'm prettty sure you need to configure the listner.ora file to point to all the places that connect to the listener, but I haven't dealt with this is in a rather long time. I'd try to add the new environment's information to the listener.ora file in addition to the old environment's info.

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

skaggs.andrew SSCrazy Points: 2474 More actions · Answer 9

sgmunson - Thursday, May 17, 2018 11:08 AM
skaggs.andrew - Thursday, May 17, 2018 8:09 AM
I'm wondering, do you think this has to do with the listener.ora file? In my current environment, I can make this connection and run my packages no problem, but I'm working on a migration. Could it be that the listener isn't configured for this new environment?
I'm prettty sure you need to configure the listner.ora file to point to all the places that connect to the listener, but I haven't dealt with this is in a rather long time. I'd try to add the new environment's information to the listener.ora file in addition to the old environment's info.

I'm working with my client to see if this fixes the issue. I will update afterwards.

aaron.reese SSChampion Points: 13498 More actions · Answer 10

Its been a while since I connected to Oracle. Have you looked at the ODBC drivers from Attunity - I found them to be serveral orders of magnitude faster than Oracles own drivers and they were easier to configure.

skaggs.andrew SSCrazy Points: 2474 More actions · Answer 11

aaron.reese - Monday, May 21, 2018 7:28 AM
Its been a while since I connected to Oracle. Have you looked at the ODBC drivers from Attunity - I found them to be serveral orders of magnitude faster than Oracles own drivers and they were easier to configure.

I have not, but will look into this as well. Thanks