Setup Of Oracle Attunity Connectors

Fairly often I come across a project where there is a requirement to extract data from an Oracle source. Whilst this can be done in SSIS using a standard ODBC connection there are some drawbacks to this – the main one for me is that when working with larger data volumes the speed of data transfer can be quite slow.

As such I have found the best solution is generally to setup the Attunity Connectors in order to facilitate quick extraction from the Oracle system. For a great comparison on the performance between straight ODBC and Attunity – please see this post from Len Wyatt.

Unfortunately the setup can be a little bit fiddly so I thought I would document the process here (as much for me as anyone else).

1. The first step is to determine the version of the Attunity connectors required – this depends upon your SQL server version:

SQL Server Version	Attunity Connector Version	Supported Oracle Versions
SQL Server 2016	v4.0	Oracle 10.x; Oracle 11.x; Oracle 12c
SQL Server 2014	v3.0	Oracle 10.x, Oracle 11.x, Oracle 12c
SQL Server 2012	v2.0	Oracle 9.2.0.4 and higher, Oracle 10.x, Oracle 11.x
SQL Server 2008 / 2008 R2	v1.2	Oracle 9.2.0.4 and higher, Oracle 10.x, Oracle 11.x

2. Following this you need to install the Oracle client. Generally you will be working in a 64-bit environment which will require both the 64-bit and 32-bit versions of the Oracle client to be installed (Data tools uses a 32-bit connection but deployed packages should run in 64-bit mode for optimum performance). The Oracle client can be downloaded from http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html you will need to sign up on the site and create an account if you don’t have one already.

NOTE: It is not enough to only download the connectors for SSIS and run the installation packages in order to retrieve data you must install the Oracle client.

Once you have determined the correct version of the Attunity connectors this you will need to select the relevant version of the Client which you wish to use. Because when Visual Studio is used to run SSIS package this runs in 32-bit mode, you will need the 32-bit version of the client installed on development machines. However, when you deploy the packages and run them from the SSIS Catalog this runs in 64-bit mode so you need to install the 64-bit version to get the best performance in production environments.

There are several version of Oracle clients available for download on the site, most recently I used the following version of the Oracle components. I find that these install relatively simply and provide the required features without requiring to much disk space or consuming to much in the way of resources.

Depending on the version of the client you have selected the installation instruction for Oracle will vary. However I would suggest you follow the following points:

1. Run the installation packages as an administrator by right clicking and selecting ‘Run as Administrator’ – previously when I have not done this it has led to unusual results.

2. You will need to install the clients on all machines which will be running the SSIS packages – this may be several servers including developer machines used for creating SSIS packages and servers used for schedules ETL runs.

3. The installation should be done in the following order:

– Install Oracle 32-bit Client (if required)
– Install Oracle 64-bit Client (if required)
– Restart machine

Once the client is installed a simple way to test that the client is working is to use SQL Developer to open the connection to the Oracle database. Once this is working correctly you then need to install the Attunity connectors.

Again depending on the system you are working on you will need to install either the 64-bit of 32-bit Attunity components for SSIS. However here the rules are slightly different.

– If you need to run in 64-bit or 64-bit and 32-bit mode, you should install only the 64-bit version of the components.
– If you need to run in 32-bit mode only then you should install only the 32-bit version of the compoents.

The latest version of the connectors (for SQL 2016) are available from: https://www.microsoft.com/en-us/download/details.aspx?id=52950

The installation of the components is pretty straightforward – once you downloaded them you run the executable and then restart the machine.

Once completed the Attunity components will be available for selection within the SSIS toolbox in the Data Flow as per the below. The connectors are a very versatile component and support many of the features you would expect from an SSIS component. Be aware that if you are looking for expressions, then these are to be found on the Data Flow Properties rather than the properties of the actual component.