OLEDB and Attunity both are slow with CLOB IN SSIS

  • Hi Guys

    I need you help again

    I am using Auutinit drivers to extract the data from Oracle Table. One of the column in the table has clob data type which is causing the data flow to extract the data very slow. The oracle source give me data like 50 rows per second. 

    Is there any tip or hidden secret to deal with clobs in Oracle ?

    Please help

  • Trick is "do not use SSIS".
    SSIS is very slow with clobs regardless of the driver used (possible exception of third party ones like CozyRoc and similar as i do not know those).
    Setting up the temp lob folder on SSD drives will speed it up but will always be slow compared to other data types

    Try and do a straight C# - source Oracle OleDb, destination SQL Server bulkcopy. This will most likely be faster and you may be able to use stream mode although I have never tried it with Oracle to Sql.
    This can be done inside a script task on SSIS.

  • frederico_fonseca - Monday, July 30, 2018 5:22 AM

    Trick is "do not use SSIS".
    SSIS is very slow with clobs regardless of the driver used (possible exception of third party ones like CozyRoc and similar as i do not know those).
    Setting up the temp lob folder on SSD drives will speed it up but will always be slow compared to other data types

    Try and do a straight C# - source Oracle OleDb, destination SQL Server bulkcopy. This will most likely be faster and you may be able to use stream mode although I have never tried it with Oracle to Sql.
    This can be done inside a script task on SSIS.

    Thanks frederico_fonseca
    I wrote C# code and used data reader and its still slow. Don't know what other option are available 

    Any idea ?

  • Could you post the C# code here so we can have a look at it and eventually suggest any modification.

    And it will always be slow - just not as slow as SSIS.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply