Hadi Fadlallah
This image shows the OLE DB providers list found in Visual Studio 2017

SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET

October 4, 2019 by

In this article, I will first give an overview of the OLE DB, ODBC, and ADO.NET SSIS connection managers. Then I will try to illustrate the difference between them when trying to connect to SQL Server.

This article is the 8th article in the SSIS feature face-to-face series which aims to remove confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.

SSIS Connection Managers

In general, SSIS connection managers are used to configure a connection between SSIS and an external data source. There are different types of connection managers that are included in SSIS.

In this article, I will mention the SSIS connection managers listed in SQL Server Data Tools for Visual Studio 2017 with their description (The following table is taken from Visual Studio “Add SSIS Connection Manager” dialog):

Connection Manager

Description

ADO

Connection manager for ADO connections

ADO.NET

Connection manager for ADO.NET connections

CACHE

Connection manager for cache

DQS

Connection manager for DQS Server (Data Quality Services)

EXCEL

Connection manager for Excel files

FILE

Connection manager for files

FLATFILE

Connection manager for flat files

FTP

Connection manager for FTP

Hadoop

Connection manager for Hadoop

HTTP

Connection manager for HTTP connections

MSMQ

Connection manager for the Message Queue task

MSOLAP100

Connection manager for Analysis Services connections

MULTIFILE

Connection manager for multiple files

MULTIFLATFILE

Connection manager for multiple flat files

ODATA

Connection Manager for ODATA Services

ODBC

Connection manager for ODBC connections

OLEDB

Connection manager for OLE DB connections

ORACLE

Connection manager for Oracle connections

PowerQuery

Connection manager for Power Query Source

SMOServer

Connection manager for SQL Server transfer tasks

SMTP

Connection manager for Send Mail Tasks

SQLMOBILE

Connection manager for SQL Server Compact connections

WMI

Connection manager for the WMI tasks

SSIS connection managers are can be added from the connection managers tab located in the bottom of the main screen:

This image shows the SSIS connection managers tab in the SSDT main screen

Figure 1 – SSIS connection managers tab

As mentioned in the SSIS connection managers tab, add a new connection manager, right-click inside the tab panel, and select the connection manager type from the context menu strip.

This image shows the add SSIS connection manager context menu strip

Figure 2 – Add SSIS connection manager context menu strip

The context menu strip contains a few types of connection managers, to see all types click on New Connection

In the following sections, we will describe only the OLE DB, ODBC, and ADO.NET connection managers since they are the ones used to connect to SQL Server.

OLE DB connection manager

OLE DB stands for Object Linking and Embedding, Database. It is an API designed by Microsoft, that allows users to access a variety of data sources in a uniform manner. Among all SSIS connection managers, OLE DB connection managers are the most popular.

When you click on Add OLE DB connection in the context menu above, the following window appears:

This image shows the defined OLE DB connections form where you can add and delete connections

Figure 3 – Defined OLE DB connections form

In this window, all previously defined connections are listed with their properties. To add a new connection, you have to click on New button. The following image shows the main OLE DB connection configuration form:

This image shows the OLE DB connection manager editor form

Figure 4 – OLE DB Connection manager editor

If we click on the Provider drop-down list, all available data sources providers are shown:

This image shows the OLE DB providers list found in Visual Studio 2017

Figure 5 – OLE DB providers list

The main OLE DB connection properties are:

  • Provider: The OLE DB provider used to connect to the data source
  • Server name: The Server that you want to connect to
  • Authentication type: The security parameters used to establish the connection
  • Database Name: The database name that we want to connect to (if this property is not specified, the default database is used)

For more information about OLE DB connection managers, you can refer to the following official documentation:

In general, OLE DB connection manager is used in all tasks and components that can connect to an external database such as:

  • Execute SQL Task
  • Execute T-SQL Task
  • OLE DB Source
  • OLE DB Destination
  • OLE DB command
  • Look up Transformation

ODBC connection manager

ODBC stands for Open Database Connectivity. It is a standard API used to access database management systems. ODBC provides access only to relational databases and they are used by OLE DB to access SQL-based data sources.

ODBC SSIS connection managers are also popular and they are used when data sources are defined as DSN (Database Source Name) in the operating system.

To add an ODBC connection manager, right-click inside the connection manager tab panel. Click on New Connection button. The following form will appear:

This image shows the defined ODBC connection where you can add anda delete connections

Figure 6 – Defined ODBC connections form

This form contains all ODBC connections added previously. To add a new one, click on New button. The following image shows the ODBC connection manager configuration form:

This image shows the ODBC SSIS connection manager editor form

Figure 7 – ODBC connection manager editor

The ODBC Connection can be defined by selecting a data source name (DSN) defined in the operating system, or by directly writing the connection string. In addition, you may have to provide the login information.

To learn more about creating and configuring DSN, you can refer to the following links:

ODBC connections are mainly used in the ODBC flow components. You can refer to the following official documentation for more information:

ADO.NET connection manager

ADO.NET stands for ActiveX Data Objects using .NET framework. It is a set of classes that expose data access services for .NET Framework programmers.

Similar to the previous SSIS connection managers, the ADO.NET connection manager allows accessing external data sources, but it uses .NET providers to establish connections.

ADO.NET connection manager editor is very similar to the OLE DB Connection manager editor as shown in the image below:

This image shows the ADO.NET connection manager editor form

Figure 8 – ADO.NET connection manager editor

It has the same connection properties such as the provider, server name, authentication, and database name.

For additional information about ADO.NET connection manager, you can refer to the following official documentation:

The ADO.NET connection manager is mainly used by Execute SQL Tasks and ADO.NET Source and ADO.NET destination.

OLE DB vs ODBC vs ADO.NET

First of all, it is good to know that ADO.NET used OLE DB providers to access data while OLE DB uses ODBC to access relational databases.

After giving an overview of these three SSIS connection managers, I will try to illustrate some of the differences between them from an SSIS development perspective.

SQL Syntax

The SQL Syntax may differ between these connections types, especially when using parameters. As example, if we need to use parameters within an Execute SQL Task, each connection type has its own syntax as illustrated in the table below:

Connection Type

Marker

Parameter name

ADO.NET

@<parameter name>

@<parameter name>

OLEDB

?

0,1,2,3 …

ODBC

?

1,2,3 …

Data Sources

While ADO.NET and OLE DB can access the same data sources, ODBC can only access relational databases systems and they cannot access XML-based sources, Microsoft Exchange Server, and others.

On the other hand, ODBC may be required when you don’t have the connection information and you are asked to use local DSN to establish the connection.

Performance

I will run my own experiments to check the difference between these connection types since you can find a lot of experiments made that illustrate the difference. One of the amazing articles providing information about that is the one written by Gilbert Quevauvilliers on Datatelblog.

As a summary, the experiments show that when connecting to SQL Server database, OLE DB guarantees higher performance in extracting and loading data.

Discussion

After showing some of the differences between these SSIS connection managers, you can see that choosing one to use can differ based on the logic you are trying to implement. But, after working for years with SSIS, I can recommend using OLE DB connection managers for one main reason, which is popularity. Since you may find a lot of resources while troubleshooting while you will not find using ADO.NET and ODBC connection managers.

In addition, there are many other differences that can be listed, you can check the external links below to get more information.

External Links

Table of contents

SSIS OLE DB Source: SQL Command vs Table or View
SSIS Expression Tasks vs Evaluating variables as expressions
SSIS OLE DB Destination vs SQL Server Destination
Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types
Execute SQL Task in SSIS: Output Parameters vs Result Sets
SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET
SSIS Flat Files vs Raw Files
SSIS Foreach Loop vs For Loop Container
SSIS: Execute T-SQL Statement Task vs Execute SQL Task
SSIS Lookup transformation vs. Fuzzy Lookup transformation
SSIS Pivot transformations vs. Unpivot transformations
SSIS Merge Join vs. Merge Transformation
Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View
SSIS XML Source vs XML task
SSIS Script task vs. Script Component
SSIS term extraction vs. term lookup
Hadi Fadlallah
ETL, Integration Services (SSIS), SSIS monitoring

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views