Can't Get Attunity Oracle Connector to Work with SQL Server 2017 / Oracle 12c

  • brad.mccollum

    SSCommitted

    Points: 1723

    I've tried many times to install the various versions of the Attunity Data Connector to allow easier interaction with data in our Oracle 12c database, which is subsequently used to populate tables in a SQL Server 2017 database... using SQL Server Integration Services.

    I've had no luck getting the resulting Attunity Data Connector icons to show-up in the SSIS Toolbox within Visual Studio 2017.

    On my development machine, I'm running the Enterprise version of SQL Server 2017.

    The development machine's OS is Windows 7.

    On the development machine, I'm running Microsoft Visual Studio 2017.

    The Oracle DB that I'm interacting with is Oracle 12c.

    I've tried installing the Microsoft Connectors for Oracle (versions 2, 3, 4, & 5) on the development machine and I always get the message of:

    "Microsoft SQL Server is not installed". "The English version of the connector will be installed"

    After the resulting installation of the Microsoft Connectors for Oracle completes and I've rebooted the development machine, the Attunity components of "Oracle Destination" and "Oracle Source" *never* do appear within the SSIS Toolbox when I've opened my SSIS project in Visual Studio 2017.

    Over the last 6 or so months, I've had to develop several staging tables in Oracle in order to unnecessarily populate these tables with data from SQL Server, in order to then use SSIS to query from the imported data from SQL Server that's now in those Oracle staging tables to successfully update data in several "final" Oracle tables.

    One of these SSIS jobs deals with a table that has several million records, which takes about 30 min. to get these records from SQL Server using the built-in SSIS ODBC Connector and populate the staging table in Oracle. I'd of course like to instead use the Attunity connectors to avoid having to create these staging tables which take time & storage space to populate.

    Any thoughts on this are very appreciated.

  • Martin Schoombee

    SSCoach

    Points: 19025

    Hi Brad,

    When it comes to Oracle I've had better luck installing the version-specific Oracle ODBC drivers, creating a System DSN and then using that in SSIS. The Microsoft SSIS driver for Oracle is pretty bad.

    That being said and not being sure if you've tried these yet, here are my thoughts and or items I'd consider:

    • Could it be a Windows 7 compatibility issue? I'd check to make sure, as many 3rd-party component just do not support Win 7 any more.
    • It's been a while since I've used Attunity connectors, but it has been my experience at times that the component is installed successfully but just not visible in SSDT. If you go to Tools > Choose Toolbox Items > COM Components you may be able to find the DLL and add it that way.
    • Check Add/Remove Programs in Windows to see if the Attunity Software is actually installed. Seeing that it's a third-party SSIS component I'm not 100% sure if it will pop up there as a separate app, but it may be worth checking anyways.
  • frederico_fonseca

    SSChampion

    Points: 14212

    sometimes you need to manually add them to the toolbox - see https://www.sqlservercentral.com/forums/topic/using-attunity-connectors-to-connect-to-oracle

    And in some versions there was a need to tweek a registry entry.

    also make sure you have the correct version of SSDT - not all work and you may need to try more than one.

    for oracle 12c and VS 2017 you need version 5 -

    if your PC is 64 bit install the 64bit version of the driver - this will install both 32 and 64bit drivers - otherwise install the 32 bit version.

    on the server install the 64bit version of the driver.

    also make sure you have the correct oracle client installed including non default options - oledb, odbc and the likes.

     

    finally - on your dev server you should have the developer edition - not the enterprise version unless you are paying for its license.

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

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