Infrastructure at your Service

Yann Neuhaus

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

Blog_Julien-architecture

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 :

 
Oracle Database Data Types SSIS Data Types
VARCHAR2 DT_STR
NVARCHAR2 DT_WSTR
NUMBER DT_R8
NUMBER (P,S) DTNUMERIC(P,S)
DATE DT_DBTIMESTAMP
RAW DT_BYTES
CHAR DT_STR
TIMESTAMP DT_STR

 

Unsupported types:

Oracle Database Data Types SSIS Data Types
LONG Not Supported
LONG RAW Not Supported
ROWID Not Supported
User-defined type
(object type, VARRAY, Nested Table)
Not Supported
REF Not Supported
CLOB, NCLOB, BLOB, BFILE Not Supported
UROWID Not Supported

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

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

003

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.

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

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

006c
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.

007
You can now test the connection.

08

 

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.

016b
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…

019

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

021

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

022
If the package is working, you should see this:

025b

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.

026b

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.

30

You can still run the package manually…

31
…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.

006c    0032b

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:

Rows OLE DB Provdier for Oracle Attunity Connectors Gain
500 rows 164 ms 152 ms 7%
1’000 rows 212 ms 207 ms 2%
2’500 rows 243 ms 232 ms 4%
5’000 rows 294 ms 274 ms 7%
7’500 rows 341 ms 307 ms 10%
10’000 rows 544 ms 475 ms 13%
50’000 rows 1147 ms 987 ms 14%
100’000 rows 1947 ms 1659 ms 15%
500’000 rows 5513 ms 4687 ms 15%
1’000’000 rows 11264 ms 9554 m 15%

 

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

1. Time/Rows:

diagram_time

2. Gain/Rows:

diagram_gain

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.

032

  • 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”.

0033

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
 

2 Comments

Leave a Reply


5 × nine =

Yann Neuhaus
Yann Neuhaus

Chairman of the Board, Chief Sales Officer (CSO), Region Manager