locked
SSIS and Oracle performance RRS feed

  • Question

  • HI Everyone

    I have an ssis package that read data from Oracle 11.2g my problem is that the package takes forever(hours and sometimes it hangs) to return data however when I create a Linked server on my local machine pointing to the Oracle server and running queries on the Linked server connection the data is dramatically returned quicker.Can someone tell me why is SSIS slow while the connection on the linked server is faster and what can I do to make SSIS return data faster the same way as the Linked server connection.

    Thank you in advance.


    newbie

    Wednesday, January 15, 2014 1:00 PM

Answers

  • Hi newbie,

    It might be related to the .Net OracleClient Data Provider. I suggest that you try the .Net Providers for OleDb\Microsoft OLE DB Provider for Oracle or .Net Providers for OleDb\Oracle OLE DB provider instead.

    If you are using SQL Server 2008 Enterprise Edition, you can also install the Microsoft Connectors v1.2 for Oracle and Teradata from:
    http://www.microsoft.com/en-in/download/details.aspx?id=29284 

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Globalwarmimg Friday, January 17, 2014 2:12 PM
    Friday, January 17, 2014 9:50 AM

All replies

  • Which connection manager are you using in SSIS?

    Which version of SSIS are you using?

    Are you using Attinuity connectors?

    http://www.microsoft.com/en-in/download/details.aspx?id=29283

    http://www.microsoft.com/en-in/download/details.aspx?id=29284


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, January 15, 2014 1:13 PM
  • Hi Visakh16

    Thank for you response.

    I am using ADO NET Source

    under driver/provider i used : .Net Providers\OracleClient Data Provider


    newbie

    Wednesday, January 15, 2014 2:29 PM
  • 0

    Hi Visakh16

    Thank for you response.

    I am using ADO NET Source

    under driver/provider i used : .Net Providers\OracleClient Data Provider

    SSIS version:10.0.2531.0


    newbie

    Wednesday, January 15, 2014 2:30 PM
  • Hi newbie,

    It might be related to the .Net OracleClient Data Provider. I suggest that you try the .Net Providers for OleDb\Microsoft OLE DB Provider for Oracle or .Net Providers for OleDb\Oracle OLE DB provider instead.

    If you are using SQL Server 2008 Enterprise Edition, you can also install the Microsoft Connectors v1.2 for Oracle and Teradata from:
    http://www.microsoft.com/en-in/download/details.aspx?id=29284 

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Globalwarmimg Friday, January 17, 2014 2:12 PM
    Friday, January 17, 2014 9:50 AM
  • Hi Mike

    Thank you for your response I used Native OLEDB\Microsoft OLE DB Provider for Oracle with OLE DB source and the improvement was dramatic.I guess dot Net providers are not the best however i must also mention that after using the Native provider  datatype 'Ntext' was suddenly not supported I had to cast it to varchar to get things going.

    best regards

    Globalwarming 


    newbie

    Friday, January 17, 2014 2:12 PM