sql-serveroracle-databasessisetlattunity

Problem trying to run SSIS package with Oracle Connection Attunity

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:

  1. When running using the Native OLE DB source from SSIS it works (but I want to use the attunity as I read is faster)
  2. Server version is: Microsoft SQL Server 2014 - 12.0.2548.0 (X64) on Windows NT 6.3 - Microsoft SQL Server Developer (64-bit)
  3. I tried to install the Attunity components from here: https://www.microsoft.com/en-us/download/details.aspx?id=55179 but both in my local computer as in the server I get an error saying that it does's find SQL Server installed, however installation seems to finish succesfully after this screenshot below:

enter image description here (I do get some error registering dlls:

---------------------------
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

enter image description here

  1. I'm running Visual Studio 2017 Professional 15.9.16 with the following among others:

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 12.2.0.1.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 [343]     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.

UPDATE 2

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:

TNS_ADMIN: C:\Oracle\product\product\11.2.0\client_64bit\network\admin

I don't have more set, shall I have something more?

Solution

  • The error was being caused on the package trying to run in 32 bit mode. I changed the package settings to 64bit and the SQL Agent settings to run in 64 bits and it started running, it works perfect now :)