SSIS Oracle Source - DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Question

SSIS Oracle Source - DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Deepak 121

Hi All,

I'm using Visual Studio 2019 to get data from a Oracle Database and transfer it to a SQL 2019 database. I'm using Oracle connection (Connection Manager for Oracle Connections) for my source and an OLE DB connection for my SQL 2019 database. Test connection work fine for both and when I execute the package in VS2019, it works fine.

When I deploy it to SQL2019 and run it from a agent job, I get a DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error. I have seen some forums and done following changes,

In VS2019 Run64BitRuntime to false
In agent job, checked it to run in 32bit mode
Installed MicrosoftSSISOracleConnector on the server containing SQL2019

Tired by setting DTS property to EncryptSensitiveWithUserKey and with password too.
Also tried DelayValidation with True and with False.

But no joy so far. Has anyone troubleshooted this in past and can shed some light on this please?

Deepak

Accepted answer

1 additional answer

Your answer

Answer 1

Yitzhak Khabinsky 26,586

Hi @Deepak ,

The Microsoft Oracle Microsoft Connector for Oracle V1.0 is not installed by default with the SSIS run-time bits. It needs to be manually downloaded and installed separately on its own on a server.

Additional info: Microsoft Connector for Oracle

UPDATE
It is better to use an EZ connect format for a connection.
You need to put the EZConnect format inside the TNS service name.
It is documented here: configuring-the-oracle-connection-manager

As end result the entire connection string will have the following four attributes:
SERVER=//host:port/service_name;
USERNAME=username;
PASSWORD=password;
WINAUTH=0;

Deepak 121 Reputation points

2021-04-21T09:57:01.517+00:00

Hi @Yitzhak Khabinsky ,

I have installed the Microsoft Connector for Oracle V1.0 on the server. Still no joy.
Yitzhak Khabinsky 26,586 Reputation points

2021-04-21T12:02:29.303+00:00

@Deepak ,

It is better to use an EZ connect format for a connection.
Are you using EzConnect format: [//]host[:port][/service_name]?

As end result the entire connection string will have the following four attributes:
SERVER=//host:port/service_name;
USERNAME=username;
PASSWORD=password;
WINAUTH=0;
Deepak 121 Reputation points

2021-04-21T13:48:52.733+00:00

Hi @Yitzhak Khabinsky

Where can I give EZConnect format? I used Connection Manager for Oracle Connections and it only has options for TNS. I tried adding a 10kb .png file attachment, but it failed. Here's the the screen shot,

https://drive.google.com/file/d/1Sjl87eLlFUZzs-TDxkwhTPjYJhEjl6HD/view?usp=sharing

Kind Regards,
Deepak
Yitzhak Khabinsky 26,586 Reputation points

2021-04-21T14:03:25.78+00:00

@Deepak ,

You need to put the EZConnect format inside the TNS service name.
Deepak 121 Reputation points

2021-04-21T14:41:22.553+00:00

Hi @Yitzhak Khabinsky

I managed to use the EZConnect method. I deployed it to SQL 2019 and getting a different error this time.

Error: The SSIS runtime version 15.0.2000.5 is too low for this Oracle connector. Please install a newer version of SSIS (later than SQL Server 2019 RTM).

I have requested the DBA's to fix this and will try again soon.

Kind Regards,
Deepak
Yitzhak Khabinsky 26,586 Reputation points

2021-04-21T14:51:11.73+00:00

@Deepak ,

Glad to hear that you are making a progress.
Your SQL Server 2019 build is RTM, i.e. very old.
You can always check SQL Server versions here:
https://sqlserverbuilds.blogspot.com/
Deepak 121 Reputation points

2021-04-22T09:59:57.01+00:00

Hi @Yitzhak Khabinsky

The version of SQL 2019 is15.0.2080.9. Is that still old?

Progress so far:
The DBA update the SSIS version, SSIS runtime version error gone, but have other errors,

CheckConnection:Error: Failed to acquire connection "XXXLive.User1". Connection may not be configured correctly or you may not have the right permissions on this connection.

The requested OLE DB provider OraOLEDB.Oracle.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Deepak 121 Reputation points

2021-04-22T10:04:37.743+00:00

So I configured it to 32bit mode and installed 32bit connector on SQL server MicrosoftSSISOracleConnectorX86 and I got the following errors,

Error: TableName failed validation and returned error code 0xC004800B.

Transfer_TableName:Error: Cannot find the connection manager with ID "{CAD6F338-92E9-4DB1-92A0-5DE61F866EC7}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by SourceTable.Connections[ORACLEConnection] in the connection manager collection of
SourceTable"". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.

Error: The connection "{CAD6F338-92E9-4DB1-92A0-5DE61F866EC7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error: The version of SourceTable, clsid {B8AB5B89-114A-409E-8626-2BABB17A356D} is not compatible with this version of the DataFlow.

Stuck again!!
Deepak 121 Reputation points

2021-04-22T10:35:36.907+00:00

Just checked and the DBA said he updated the SQL and it's on (15.0.4123.1, April 2021)

Still same errors.
Yitzhak Khabinsky 26,586 Reputation points

2021-04-22T13:02:57.53+00:00
@Deepak ,

SQL Server build 15.0.4123.1 is good. It is the latest CU10.

If you are on the server it is better to use 64-bit MS Oracle Connector.

It is impossible to suggest anything meaningful, you need to share how Oracle connection is configured in both places: Visual Studio as well as SSIS Catalog on the server.
Deepak 121 Reputation points

2021-04-26T15:13:32.157+00:00

Hi @Yitzhak Khabinsky ,

It finally worked. The EZConnect, 64bit driver and update to latest SQL build did that trick. Thank you so much for your suggestions :-)

Kind Regards,
Deepak

Answer 2

Monalv-MSFT 5,926

Hi @Deepak ,

May I know if you download the oracle connector for both 32bit and 64bit?

Please refer to Microsoft Connector for Oracle.

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Monalv-MSFT 5,926 Reputation points

2021-04-15T09:40:48.147+00:00

Hi @Deepak ,

May I know if you have anything to update?

Best regards,
Mona
Deepak 121 Reputation points

2021-04-21T09:59:52.013+00:00

Hi @Monalv-MSFT ,

I enabled the package to run in 64 bit and installed 64-bit SSIS connector on the server, no joy. I also disabled 64-bit processing in VS2019 for this package and installed 32-bit driver on the server, still no joy.

Kind Regards,
Deepak

Share via

SSIS Oracle Source - DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

1 additional answer

Your answer