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:
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.
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:
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:
Figure 4 – OLE DB Connection manager editor
If we click on the Provider drop-down list, all available data sources providers are shown:
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:
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:
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:
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
- What is the difference between OLE DB and ODBC data sources?
- OLEDB V/S ODBC
- How to use SSIS ODBC source and differences between OLE DB and ODBC?
- SSIS Data Flows – ADO.NET vs. OLE DB vs. ODBC
Table of contents
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
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023