From:       To:      

How to automate exporting data from Oracle dump file to SQL Server

It is known that Intelligent Converters provides OraDump Export API to allow developers integrate corresponding capabilities into their applications. These capabilities are about export data from Oracle dump files into popular databases like Microsoft SQL server. Every developer having OraDump Export API can write just few lines of code on C# or Visual Basic to implement the appropriate export process. But what if someone requires to automate it? Obviously the proper task scheduler is necessary for this purpose. If destination database is hosted on Microsoft SQL server, SQL Server Integration Services (SSIS) would be the best choice.

However, SSIS requires that the assembly is registered in the Global Assembly Cache (GAC) and for this purpose it must be signed with "strong name" (assembly key). Only .NET components can be signed with "strong names", whereas OraDump Export API is an unmanaged COM DLL. So, it cannot be signed with "strong names" and added to GAC directly.

That's why it is necessary to wrap OraDump Export API component (ordecom.dll) into "Interop assembly" before using it with SSIS. It is possible to generate COM Interop assembly wrapper using the following steps (this manual is using Visual Basic, but the same steps could be applied for any other .NET compatible environment like C# or Java):

  1. In Microsoft Visual Studio .NET, open the properties of the Visual Basic project in which you want to reference the COM component
  2. Delete existing reference to ordecom.dll if any
  3. Create an SNK keyfile in the project's directory:

    sn.exe -k KeyFile.snk
  4. Run Tlbimp.exe from a command prompt against the original COM DLL to generate an Interop assembly with strong name:

    tlbimp.exe ordecom.dll /keyfile:KeyFile.snk /out:MyCOMInterop.dll
  5. Add a reference to the output file from Tlbimp.exe (MyCOMInterop.dll in the sample above) to Visual Basic .NET project.
  6. Rebuild the project.