SQL Server: Attunity connectors for data transfer to Oracle

Recently, at a customer site, I discovered the Attunity connector 1.1 for Oracle. The customer uses this connector to transfer data from Oracle to SQL Server using Integration Services (SSIS) and informed me that performances are greatly improved. So I decided to post a blog about this connector and perform a benchmark between Attunity connector and Microsoft OLE DB provider for Oracle.

Architecture

All along this blog, I have used this 2 virtual machines:

vmtestsqlwin01:

• IP: 172.22.1.192
• OS: Windows Server 2008 R2
• Software: SQL Server 2008 R2 SP1 (Engine, Integration Services & Microsoft Visual Sutdio)

vmtestoradg2:

• IPI: 172.22.1.175
• OS : Oracle Enterprise Linux (OEL)
• Software : Oracle 11gR2

Prerequisites

Attunity connector dose not work with all version of SQL Server, be sure that all prerequisites are met.
The connector does not support all types of data (~50%). Undermentioned is the array of the supported types :

Unsupported types:

You have to use Microsoft SQL Server Enterprise Edition.
You will need Microsoft Visual Studio or Business Intelligence Development Studio (BIDS) from SQL Server 2008, connector is not compatible with SQL 2005.
An Oracle Client has to be installed and you have to define a TNS Service Name.

Attunity connector installation

The connector has to be installed on the same server than SSIS.
The connector can be downloaded at this address: http://www.microsoft.com/download/en/details.aspx?id=13921
Launch the executable and follow the instructions, set the installation path and confirm the installation.

To finish the installation, SQL Server Integration Services service must be restarted.

Now, you can create a new Integration Services project with Microsoft Visual Studio or Business Intelligence Development Studio (BIDS). BIDS is delivered with SQL Server
In the new package, click on the Data Flow tab and create a new one.
After installing the Oracle Connector, the Oracle source and destination should be available in the toolbox area of BIDS. If these components are not available, do the following to add them.

1. In the Toolbox, right-click on either Data Flow destinations or Data Flow Sources and select Choose items.

2. Click on the SSIS Data Flow Items tab.
3. Find the Oracle Source, Oracle Destination, or both of them in the list and select the check box for the components you want to add to the toolbox.

4. Click OK and Attunity components are availables in the toolbox.

Creation of the package

Set up an Oracle Source and a SQL Server Destination if SSIS is on the same server than Database Engine. Otherwise, use an OLE DB Destination.

Double click on the Oracle Source to configure it.
Enter the name of the connection and the following information: TNS service name and Oracle account.

You can now test the connection.

If the test connection is successfull, click Ok. Choose the table or view to be transferred to SQL server. Click Ok.

Double click on the SQL Server destination. Create a new connection such as “SQL Server Native Client 10.0”, enter the name of the server, choose the authentication mode and the database.

Click Ok and select the new connection.
You have two solutions to import data from Oracle: You can either import the data into an existing table or view…

…or in a new table with the same structure than the source table:

Click ok.
The package can be tested by clicking on the green arrow in the toolbar:

If the package is working, you should see this:

If it the package is not working, look up the section “frequents errors” to try to solve your problem.
The package is ready to be imported into SQL Server Management Studio. Launch Management Studio and connect to Intergration Services.

In the folder Stored PackageMSDB, create a folder and import your package with a right click.
In package location, choose File System. Look up your package on the server, name it, keep the protection level and click OK.

You can still run the package manually…

…or integrate it into a job or other function.

Benchmark

The aim of the benchmark is to compare the performances of Attunity connector for Oracle and OLE DB provider for Oracle.

I performed the tests with (10) tables of 500, 1’000, 2’500, 5’000, 7’500, 10’000, 50’000, 100’000, 500’000 and 1’000’000 rows.
The structure of the table is simple: 5 fields of different types : Char, Varchar2, Number, Number(x,y) and date. I populated the table with random values.

Analysis

After testing all the packages, here is the result of the benchmark:

Here are two comparative diagrams of OLE DB Provider for Oracle and Attunity connector:

1. Time/Rows:

2. Gain/Rows:

The diagram shows that the more rows there are, the more performances are improved. the connector becomes very interesting starting from ~10’000 rows.

The maximal gain is ~15% with 1’000’000 rows.

Encountered problems and frequents errors

Even if Attunity connector is a very useful tool, you may encountered some errors.

In this section, I have described the most commons errors. These errors are not specific to Attunity connector, so if you are accustomed to BIDS you may already know them.

• Error : “0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion. ”
  • Solution : Disable UAC (User Account Control) in the control panel.
• Error on OLE DB source and destination: “Cannot retrieve the column code page info on the OLE DB Provider. If the component supports the “DefaultCodePage” property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component’s locale ID will be used.”
  • “AlwaysUseDefaultCodePage” must be set on “true” on OLE DB source and destination.

• Error: 0xC0209303 and 0xC020801C.
  • You have to run the package in 32 bit mode. In the properties of the project (accessible via Project menu), set “Run64BitsRuntime” on “false”.

Conclusion

Attunity connector provides an improved performance even with few records. The gain is great if you have a lot of rows to transfer. The implementation is quite easy, so with minimal efforts you can gain a lot of time on your SSIS packages.
Considering a gain of 500 rows, using these connectors is primarily interesting if you have a lot of data to transfer.

The primary drawback is that the connector does not support all types of data, so be careful before using it. And as I have said before, you have to have Enterprise Edition, otherwise, you cannot use Attunity connector.

• Coming soon: a benchmark with Microsoft SQL Server 2012 and Attunity connectors 2.0