Let’s imagine you are a wise DBA , and you have a brand new shiny SQL Server 2008 R2 DB on a Win server 2008 R2 , 64 bit OS platform … and for some INSANE reason you have to move A LOT ( we talk about some millions of rows ) of data from an Oracle server to this DB … what would you do ?

Before today my answer would have been “Install the appropriate (64bit) Oracle client with  OLE Db provider for Oracle and use it as data source in the MSSQL Import (AKA DTS)” …

But, of course, the fight between Oracle and Microsoft will not make things so easy … so this solution will not work : Microsoft OleDB provider for Oracle will return error when initializing and Oracle OLE DB Provider for Oracle will not show up in the list of the data sources; so you will not be able to use DTS …  unless… here comes the magic: you install ALSO the  32bit Oracle client !

I found this solution in an article from Jorg Klein where he suggested this   for a similar problem.

I also adjusted the registry keys  as indicated in the article … and right now my DTS import is copying rows at decent speed between the two bitter enemies RDBMS ‘s.

Advertisements