I'm having the following issue:
I have an SSIS package that connects to an oracle DB using Attunity Connection Manager.
It does connect in my local pc (to a remote oracle db server) and extracts and all, seems to work.
The problem comes when I load it to SQL server to be run as a Job. I have ran other jobs so the setup is proper, set to run with 32 bit runtime, loading as a file system type.
but when I try to execute the job it gives the following error:
Executed as user: username. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2548.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:39:28 PM Error: 2019-09-27 21:39:28.90 Code: 0xC0010018 Source: Package
Description: Error loading value "
Now, as it runs locally, I believe that it might be related with something in the SQL server where I deploy the package so I'll give some information about the setup and some notes:
--------------------------- Microsoft Connector for Oracle by Attunity --------------------------- An error occurred during registration of: "C:\Program Files\Attunity\Microsoft Connector for Oracle\50\ENU\AttunitySSISOraAdapters.dll" --------------------------- An error occurred during registration of: "C:\Program Files\Attunity\Microsoft Connector for Oracle\50\ENU\AttunitySSISOraConnections.dll" --------------------------- An error occurred during registration of: "C:\Program Files\Attunity\Microsoft Connector for Oracle\50\bin32\ENU\AttunitySSISOraAdapters.dll" --------------------------- An error occurred during registration of: "C:\Program Files\Attunity\Microsoft Connector for Oracle\50\bin32\ENU\AttunitySSISOraConnections.dll" ---------------------------
and after it
SQL Server Data Tools 15.1.61906.03120 Microsoft SQL Server Data Tools
SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 14.0.3002.113
Oracle Developer Tools for Visual Studio 18.104.22.168.0 Oracle Developer Tools for Visual Studio Copyright (c) 2005, 2017
Please let me know if I'm missing information to help pinpoint the problem. I think I might be missing a component but I don't really know what.
UPDATE: I changed the TargetServerVersion in the package properties from SQL Server 2017 to 2014 (the version I have installed) and I'm getting this error now:
Error: 2019-09-30 17:53:13.20 Code: 0x000002C0 Source: Package Connection manager "Oracle Connector 1" Description: OCI error encountered. ORA-12154: TNS:could not resolve the connect identifier specified End Error Error: 2019-09-30 17:53:13.20 Code: 0x0000020F Source: Data Flow Task 1 1 Oracle Source  Description: The AcquireConnection method call to the connection manager Oracle Connector 1 failed with error code 0x80004005. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2019-09-30 17:53:13.20 Code: 0xC0047017 Source: Data Flow Task 1 1 SSIS.Pipeline Description: Oracle Source failed validation and returned error code 0x80004005. End Error Error: 2019-09-30 17:53:13.20 Code: 0xC004700C Source: Data Flow Task 1 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2019-09-30 17:53:13.20 Code: 0xC0024107 Source: Data Flow Task 1 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:52:41 PM Finished: 5:53:13 PM Elapsed: 32.125 seconds. The package execution failed. The step failed.
I have uninstalled all I had and installed only the V3 version of the Attunity Connection Manager, still getting the error from above. At this point I don't know what could be happening. It works when I run it from my Visual Studio (2017, with all connection managers pointing to the servers) but then when I schedule it on the SQL Server Agent it give the error shown, Also, it does work well when I use OLE DB Manager, the issue comes when trying to use the Oracle Source (from Attunity)
I'm also using the Connection manager set with format: host:port/ServiceName I'm thinking I might have something badly set on the server? on terms of the Oracle setting? I have the following:
I don't have more set, shall I have something more?
You missed the explanation about getting the correct version for your version of SQL Server, perhaps. Also the part about
Instead, try the Attunity V3 drivers that can be found here:
User contributions licensed under CC BY-SA 3.0