1

Please could someone help me on similar/same issue as in this TOPIC - SSIS package that connects to an oracle DB using Attunity Connection Manager

My environment situation is:

  • DEV Virtual Machine: VS2019 Version 16.8.1 (Microsoft SQL Server Integration Services Designer Version 15.0.2000.132)
  • DEV SQL Sever: 2017 DEV (Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64))

I have installed:

  • LINK Microsoft Connectors v5.0 for Oracle and Teradata ATTUNITY (as i need SQL 2017 compatibility)

and

  • LINK Microsoft Connector for Oracle V1.0

(I have tried multiple time installing only 32 or only 64 versions, and uninstalling and restarting VMs) I have SSIS package with Oracle Connection Manager. In Visual Studio inside package:

  • IF SQL Target version is SQL 2019
  1. Connection manager to oracle test: SUCCESS
  2. Run Package: SUCCESS
  3. Deply Package to SSIS DB: Fail (Reasonable -> I have SQL 2017 :)

  1. IF SQL Target version is SQL 2017

  2. Connection manager to oracle test: SUCCESS

  3. Run Package: FAIL

    ERROR in DEBUG MODE:

[OMS OrderLines ABBICS V_F_ORDERS_RO24881SRU 3] Error: The AcquireConnection method call to the connection manager Oracle DB failed with error code 0x80004005. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[SSIS.Pipeline] Error: OMS OrderLines ABBICS V_F_ORDERS_RO24881SRU failed validation and returned error code 0x80004005.

Error: The ORACLE registry key cannot be opened. Verify that the Oracle Client for Windows x86 is installed properly.

  1. Deply Package to SSIS DB: SUCCESS
  2. Run Package on SQL Server (from SSIS Catalog or from JOB): FAIL -> Basically same errors as in package

I have tried running packages in 64 and 32 bit mode as well as running job in 32 bit mode. The only difference was

...Windows x86 is installed properly...

or

...Windows x64 is installed properly...

Thank you! Wojciech

wojjy
  • 41
  • 7
  • Maybe someone can share registry keys that should be created during installation process? – wojjy Nov 26 '20 at 07:54

1 Answers1

2

Finally I was able to solve that uninstalling everything and install what i mentioned in post above plus:

32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio

ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio (12.2.0.1.1)

https://www.oracle.com/database/technologies/dotnet-utilsoft-downloads.html

This fixed issue and package can be run in both 32 and 64 bit mode

  • The only question that remains, do i really need to install this whole package with unnecessary (for me) features? Now i have to install it on all DEV/UAT/PROD nodes :/ Is there any particular component I can install to get this up and running?

EDIT: To minimize installation components - >On machines with SQL server 2017 I did following things:

  1. Downloaded 64-bit ODAC 12.2c Release 1 (12.2.0.1.0) Xcopy for Windows x64 (ODAC122010Xcopy_x64.zip) from LINK
  2. Unzipped File on SQL VM
  3. Run CMD as admin, navigate to unzipped location and run

install.bat basic F:\ORACLE myhome false true

This will install Oracle Instant Client Machine Wide

  1. Added to system variable PATH value: F:\ORACLE\

This allows me run SQL jobs successfully (remember to install other components mentioned in main post!)

wojjy
  • 41
  • 7