Why so many connection providers for database ?

• blasto_max

  SSCertifiable

  Points: 5025

  More actions

  January 22, 2014 at 10:41 am

  #151313

  I see several connection types for databases - OLEDB, ADO, ADO.NET etc. AFAIK, ADO.NET has a convenient and easy to use API in scripts. Also, if you want to retainSameConnection inside a .NET script, its easier to use a ADO.NET result set rather than OLEDB as mentioned here - http://stackoverflow.com/questions/11867639/connect-to-sql-database-inside-script-task-in-ssis

  Then why do we have so many types of DB connection managers ? Why not choose the most superior one and have only that ? One reason could be that .NET might not be installed on target machines. If yes, then are there any valid reasons for not installing .NET on target machines ?

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  January 22, 2014 at 1:51 pm

  #1682575

  It depends on how you want to use it I guess. It's about having a choice.

  Each type of adaptor has its own strenghts and weaknesses. I've done tests where OLE DB was faster than ADO.NET.

  Should I throw away performance because someone else wants to use the connection manager in an API? No, I want to have a choice.

  Some components only work with OLE DB (lookup), some work only with ADO.NET (CDC). Some providers are only available in ODBC.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• sneumersky

  SSCertifiable

  Points: 7667

  More actions

  January 23, 2014 at 8:41 am

  #1682835

  If I am MS, I would also like to appeal to as many customers as possible 😀

  The heart and soul of an ETL tool is the ability to connect to as many sources as possible and load a large amount of data to one target.

• phil.doensen

  Mr or Mrs. 500

  Points: 501

  More actions

  January 23, 2014 at 8:15 pm

  #1682991

  The connection protocol is often decided by those that wrote the client software

  and what they decided to develop their software in:

  Roughly, but not exclusive or correct in all cases.

  OLEDB - Probably a C or a C++ program

  ADO - Visual Basic, MS Access

  ADO.NET - Microsoft .NET programming language

  ODBC - Some non-Microsoft development package

  As was said. MS is just catering for the largest audience possible.