SSIS SQL Job OleDB Oracle Connection Issue

Question

SSIS SQL Job OleDB Oracle Connection Issue

jermhillgert

SSC Journeyman

Points: 95
More actions
February 5, 2009 at 7:05 am

#127922

I have a package that I use to grab data from a Oracle OleDB Connection. The package runs in BI Studio and when I deploy the package to the server it runs on its own. When I assign it to a SQL Job I run into the following error:
Description: SSIS Error Code DTS_E_OLEDBERROR.
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
I have the protection level for the package set to 'DontSaveSensitive' and I supply the password for the connection manager in a XML Configuration File. If I disable the piece of the package that uses the Oracle Connection it runs in the SQL Job without any issues. I am at a lost at this point what the problem could be. Thanks for any help.

Viewing 8 posts - 1 through 8 (of 8 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

jermhillgert (2/5/2009)
I have a package that I use to grab data from a Oracle OleDB Connection. The package runs in BI Studio and when I deploy the package to the server it runs on its own. When I assign it to a SQL Job I run into the following error:
Description: SSIS Error Code DTS_E_OLEDBERROR.
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
I have the protection level for the package set to 'DontSaveSensitive' and I supply the password for the connection manager in a XML Configuration File. If I disable the piece of the package that uses the Oracle Connection it runs in the SQL Job without any issues. I am at a lost at this point what the problem could be. Thanks for any help.

Are you sure there no more errors other than these? Can you send the complete errors dump?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

jermhillgert SSC Journeyman Points: 95 More actions · Answer 2

This is from the error log:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ORAP.h3549" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

jermhillgert (2/5/2009)
This is from the error log:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ORAP.h3549" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Is the server machine 64bit?

Can you setup proxy account for your SQL Job Step and use the same account you say works properly when run manually (information how to setup the proxy account).

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

jermhillgert SSC Journeyman Points: 95 More actions · Answer 4

Is the server machine 64bit?
Can you setup proxy account for your SQL Job Step and use the same account you say works properly when run manually (information how to setup the proxy account).

No its not a 64 bit machine. I have already setup a proxy that is a different account but has the same permissions that my account has.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

jermhillgert (2/5/2009)
Is the server machine 64bit?
Can you setup proxy account for your SQL Job Step and use the same account you say works properly when run manually (information how to setup the proxy account).
No its not a 64 bit machine. I have already setup a proxy that is a different account but has the same permissions that my account has.

Can you try with your account as a proxy?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

jermhillgert SSC Journeyman Points: 95 More actions · Answer 6

CozyRoc (2/5/2009)
jermhillgert (2/5/2009)
Is the server machine 64bit?
Can you setup proxy account for your SQL Job Step and use the same account you say works properly when run manually (information how to setup the proxy account).
No its not a 64 bit machine. I have already setup a proxy that is a different account but has the same permissions that my account has.
Can you try with your account as a proxy?

I tried that too and it did not work. The thing that makes me think it is not an issue with the account is that I have SQL OleDB Connection Managers in the same package that run. It seems to just be an issue with the OleDB Connection Managers that are connecting to Oracle Databases.

I'm thinking that I might just use the SQL OleDB and run a openquery to link to the Oracle Database through a linked server.

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

jermhillgert (2/5/2009)
CozyRoc (2/5/2009)
jermhillgert (2/5/2009)
Is the server machine 64bit?
Can you setup proxy account for your SQL Job Step and use the same account you say works properly when run manually (information how to setup the proxy account).
No its not a 64 bit machine. I have already setup a proxy that is a different account but has the same permissions that my account has.
Can you try with your account as a proxy?
I tried that too and it did not work. The thing that makes me think it is not an issue with the account is that I have SQL OleDB Connection Managers in the same package that run. It seems to just be an issue with the OleDB Connection Managers that are connecting to Oracle Databases.
I'm thinking that I might just use the SQL OleDB and run a openquery to link to the Oracle Database through a linked server.

You have to keep in mind how the SQL Job Step executes your SSIS package. It uses light-way process execution, without loading the account "user profile". If the Oracle driver needs information from the user profile, then it will not work. Unfortunately there is no way to control if the user profile is loaded currently. I would suggest if you can try another Oracle driver instead like ADO.NET or ODBC.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/