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.

	Attunity	OLE DB	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

Using the OLE DB connectors doubled the performance of the transfer compared to the Linked Server, and using the Attunity connectors more than doubled the performance of the transfer compared to the OOTB OLE DB connectors! Wow, I was not expecting that.

Using the Attunity connectors is almost exactly the same to using the OOTB OLE DB once they are installed on your system. Simply drag them from the Toolbox onto your Data Flow Task and hook them up to your output.

The one drawback to using the Attunity connectors is that your MS SQL Server has to be the Enterprise edition. This will likely reduce the number of people able to take advantage of the connectors, but if you already have Enterprise, you are good to go!

I found a similar blog post that analyzes the Attunity connectors in more depth:

http://blogs.msdn.com/b/sqlperf/archive/2009/08/14/using-ssis-to-get-data-out-of-oracle-a-big-surprise.aspx

You can get them here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11180