Post reply

Recommended Connection Manager type for ORACLE databases

  • January 21, 2009 at 8:19 pm

    #199091

    Hi,

    I am new to SSIS 2008. I worked on SQL Server 2000 DTS packages a while ago.

    I would like to know what will be the best connection manager to use to connect to ORACLE databases(both as a source or a Target database).

    Also for SQL Server, what will be recommended connection manager.

    Thanks in Advance.

  • January 23, 2009 at 12:08 am

    #931654

    DBUSER (1/21/2009)

    Hi,

    I am new to SSIS 2008. I worked on SQL Server 2000 DTS packages a while ago.

    I would like to know what will be the best connection manager to use to connect to ORACLE databases(both as a source or a Target database).

    Also for SQL Server, what will be recommended connection manager.

    Thanks in Advance.

    I would recommend that you use the Oracle OLEDB and ADO.NET providers. They are better supported and feature-complete. You have to also keep in mind one issue with SSIS. Most of the standard components require OLEDB connection manager, but sometimes you need to use a connection manager in your script. The problem is that the OLEDB connection managers are useless from script and you will have to use ADO.NET connection manager.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • February 1, 2009 at 5:09 pm

    #936212

    Thank you.

    Actually,

    We have to copy data from ORACLE source to ORACLE Destination.

    Our developers tried both ADO.net and MS Oracle OLEDB driver first.

    We had few issues with both ADO.net and OLEDB data source.

    Our developers used ADO.net connection. They Encountered the following error while running SSIS jobs with ADO.NET data connection: "Unable to load DLL 'oramts.dll': The specified module could not be found." So the resolution they made is installed Oracle client data providers.

    When they used ORACLE OLEDB, Oracle OLE DB (Oracle driver) reported the following error with larger datasets: Value violated the integrity constraints for a column or table. Our developer could not find out the cause so they decided to use ADO .net connection type again even for ORACLE datasources.

    MS OLEDB driver for ORacle had very poor performance.

    So Finally the team decided to use ADO.net data source only.

    I would like to know whether we are going in the right direction or not.

    Would someone please reply as soon as they can so that I can talk to my developers.

    Regards

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

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