Wednesday, October 19, 2011

SSIS Attunity Connectors

Recently I had to create an SSIS package that transferred a huge amount of data between an Oracle database and a MS SQL Server database. I began creating an SSIS package that used an existing linked server connection that the client had already been using to perform similar data transfers. To my dismay, the performance of the linked server was dismal. Instead of using the linked server I decided to go with the OOTB OLE DB data source available in SSIS. This increased my performance dramatically. However, when I spoke to a colleague at TCSC, he recommended that I try the Microsoft Connectors for Oracle by Attunity. I had never heard of these connectors, but I was intrigued, so I downloaded them and ran some performance test.

The following tests results we recorded while debugging through Visual Studio, so real numbers will be better than what I have recorded, but the results do represent without a doubt the performance differences between the different connections.

Linked Server
3000 rows
2 seconds

6 seconds

10 seconds
10,000 rows
3 seconds

10 seconds

20 seconds
100,000 rows
19 seconds

1 minute 28 seconds

3 minutes 2 seconds
500,000 rows
1 minute 23 seconds

7 minutes 35 seconds

16 minutes 18 seconds
1,000,000 rows
2 minutes 38 seconds

16 minutes 37 seconds

30 minutes 28 seconds