Yesterday I had a task to transfer some data from Microsoft SQL Server 2008 x64 running on MS Server 2008 and at first I thought it will be a simple job. After all I thought it will not bi that big of a deal – what could go wrong with transferring 100K rows from one server to another (despite there are two different database engines). Before I tell you the whole story let me clear my predefined requirements for getting the job done:
- The method I use should be able to be integrated to Integration services package
- The transfer must not take more than minute per 10K rows
- The transfer must be initiated from the SQL server side with SSIS package, which means on 64-bit platform
The list is as simple as that. So my struggle began. First I tried with the simple INSERT INTO <linked Oracle Destination>. As easy that method was it was awfully slow – 10K rows were transferred for approximately 4 minutes, which of course did not meet my second requirement.
My second try was with SSIS using OLE DB Destination with the Microsoft OLE DB Driver for Oracle. NEVER DO THAT on a x64 platform. Business Intelligence studio is a 32-bit application and when you try to start the package it says there is an error with initializing the OLE DB driver. After a ton of reading I discovered that there is a collision between the 32-bit BIDS and 64-bit driver (of course there will be, my surprise was that actually there is no way using Microsoft’s driver in developing packages on x64 platform. By the way the same applies and for .NET Driver for Oracle.
So it was time for my next approach – I installed the OLE DB driver from Oracle. Well my first try was unfortunate – I received an error – TNS:could not resolve the connect identifier specified. This error is quite misleading! It took me a great deal of time to find out that I have fallen in the x64 pit again :( Anyway, after a quick search, I found a quick workaround for that case – you must install both x86 and x64 Oracle Clients and then create a simple softlink, which to be added in the environment variable PATH. Detailed description on how to do that, you can find here. This was my first break-through in finding another solution, besides the one with the linked server. I did a simple test with BIDS and 10K rows transferred for over 7 minutes. Complete disaster!
Then I figured if BIDS is a 32-bit application, why I do not use 64-bit application to create the package for transfer, SSMS for example. After all when you use Export and Import Wizard, you can save the package directly and the just execute it (with dtexec utility, of course :-) ). It was that easy…but again very slow. I used Oracle OLE DB driver, Microsoft’s OLE DB for Oracle driver, Microsoft’s .NET provider for Oracle, etc. I could not get the data to be transferred in less than 6 and a half minutes.
After trying so many methods and all of them so exhausting till I get to the point of performance testing them, I was on the verge of quitting (and approach with the standard file transfer to ftp, as the only secure and working way).
My last try was after doing a lot of reading and searching the web a method I have not tried yet. I have to say there are not many articles on the subject with transferring data from and to Oracle from x64 platform running Microsoft OS and Microsoft SQL Server. However, there is one product that save the day – Microsoft Connectors for Oracle and Teradata by Attunity (download it from here). Instruction on how to use it and a description of the whole product – here. But be careful to install the correct package (x86,x64 or IA), because you will find yourself searching the web and asking in forums for misleading errors like This occurs when CPackage::LoadFromXML fails. Anyway – I build my package and it was time for testing – I was pleasantly surprised, because for 10K rows it took less than a second to insert!
In conclusion if you want to build SSIS package on a x64 platform and transfer data to Oracle DB without additional overhead and slow performance, one of the best ways is to use the Attunity driver!