Wednesday, June 16, 2010

SSIS Extracting Reading from Oracle 10g

Here are a few hints if your extracting data from an Oracle database using SSIS.

1) Use Oracle 11g client. The 10g client has bugs around the naming of the Program Files directory on 64-bit machines. It doesn't like the parantheses in Program Files (x86). There are workarounds for using the 10g client but it's been my experience that it's not worth the effort.

2) Make sure to install the 32-bit client for running SSIS in the IDE (debug mode) and the 64-bit client for the 64-bit SSIS runtime.

3) Use the Attunity connector for Oracle. This component can extract data up to 100 times faster than out of the box OLE DB for Oracle provider.

2 comments:

  1. Hello,

    I am a newbie to SQL server. Currently we source table from oracle and drop/create in SQL server using manualy generated SSIS package. Problem is when something changes in oracle, SSIS doesn't reflect the new changes. Is there a way to auto generate SSIS pakcage?

    Thanks,
    Ravi

    ReplyDelete
  2. Unfortunately there is no way to auto-generate a package (without a lot of effort and in an unsupported manner). Just as you could with most OOP or scripting languages you could create a script task using VB.NET or C# to programatically extract the table definition from the Oracle database and build the SQL Server create scripts based on this definition.

    ReplyDelete