none
Provider to use for faster loading

    Question

  • hi im moving data from

    Source: Oracle database which is in different server

    Target:SQL

    im confused to use which one is best to use for largescale datamigration

    1.Microsoft OLEDB Provider for Oracle

    2.Oracle Provider for OLEDB

    3.Oracle Client data Provider(ADO.NET)

    4.Oracle Data Provider for .Net

    please let me know the difference 

    Wednesday, December 28, 2016 3:59 PM

All replies

  • Hello,

    For performance reason SSIS is written in unmanaged (non .NET) code, as OleDB provider are, so you get best performance by using OleDB (or ODBC) data provider = 1. + 2.

    When using .NET (=managed code) data provider, SSIS must switch between unmanaged and managed code and this will slow it down.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, December 28, 2016 5:32 PM
  • Also there's a direct SSIS provider built directly on OCI, which is definitely faster for loading Oracle, and may be faster for reading too.

    Microsoft Connectors v4.0 for Oracle and Teradata

    David


    Microsoft Technology Center - Dallas

    My Blog

    Wednesday, December 28, 2016 5:47 PM
  • You need to use the Attunity Oracle driver for the highest performance.

    Please see:

    http://www.attunity.com/microsoft-connectors-by-attunity

    Wednesday, December 28, 2016 5:49 PM
  • +Tom's suggestion.

    I've had good luck using the Attunity driver as far as providing the highest performance possible for ETL from Oracle to SQL solutions.

    Thanks,


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, December 28, 2016 10:22 PM
  • im using SQL2005 so i could not use these drivers.
    Thursday, December 29, 2016 10:16 AM
  • You need to upgrade to a supported version of SQL Server.

    In SQL 2005 your only real choice is to use the Oracle Provider for OLEDB.  Prepare yourself, it will be slow.

    http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

    Thursday, December 29, 2016 12:55 PM