Difference between OLE DB and ODBC in SSIS

I thought I’d collect some notes on the difference with regard to SSIS, as we get asked from time to time to recommend which to use.

ODBC

ODBC stands for Open Data Base Connectivity, which is a connection method to data sources and other things. It requires that you set up a data source, or what’s referred to as a DSN using an SQL driver or other driver if connecting to other database types. Most database systems support ODBC.

ODBC is designed for connecting to relational databases.

 Using ODBC with MS SQL Server is a worthwhile article for further reading.

 

OLE DB

OLE stands for Object Linking and Embedding. OLE DB is partly distinguished from OLE itself, now called automation.

OLE DB can access relational databases as well as non-relational databases.

OLE DB is the successor to ODBC, a set of software components that allow a front end such as GUI based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2, mySQL etal. In many cases the OLE DB components offer much better performance than the older ODBC.

OLE DB is a different type of data provider that came about with MS’s Universal Data Access in 1996 and does not require that you set up a DSN. It is commonly used when building VB apps and is closely tied to ADO. It works with COM, and DCOM as of SQL 7.0.