What is Open Database Connectivity (ODBC) in SSIS

ODBC stands for Open Database Connectivity and is a technology connection standard for passing data between systems that is widely used today for access to RDBMS systems that do not have an OLE DB or ADO.NET provider.

Just like the standard OLE DB providers, ODBC is part of the Windows operating system, included in the MDAC (Microsoft Data Access Components) when the operating system is installed. However, ODBC works differently than the OLE DB providers in that you need to set up the connection information through an applet in the Administrative Tools called Data Sources (ODBC). The OLE DB connections, conversely, are managed directly by the applications and not by the OS. There are some similarities in connecting to Oracle because for Oracle connections, you need to have the configuration managed external to SSIS as well.

For SSIS in SQL 2014, ODBC connectivity is handled by source and destination adapters in the Data Flow. Therefore, the process to get access to an ODBC Source or Destination is to first configure the connection in the Data Sources (ODBC) applet and then reference the ODBC connection through the ODBC adapters in SSIS.

1. The following example uses public domain data from a DBF Source file, which can be accessed through an ODBC connection. The file is a set of records containing a list of U.S. cities and their properties and is available for download with this Tutorial’s examples in a file called tl_2013_13_concity.dbf. Use the following steps to connect to an ODBC-based source:
The first step varies according to the machine on which you are working:
=>For machines with an X64 version of Windows installed, go to a run command and enter the following to bring up the 32-bit version of the ODBC administrator program: 

Because you are developing a package in SSDT, which is a 32-bit program, you need to make an entry in the 32-bit version of the ODBC administrator tool.=>If you are on a 32-bit machine, go to the Administrative Tools folder found in the Control Panel list. Then open the Data Sources (ODBC) application from this list of administrative programs. below screen shot shows the ODBC Data Source Administrator tool.

2. Switch to the System DSN tab, where you will create the ODBC reference (so it is accessible to all users) and click Add.
3. In the Create New Data Source window, scroll down and choose the Microsoft dBase Driver (not the Microsoft Access dBase Driver) and select Finish.
4. In the ODBC dBase Setup window, change the Data Source Name to US_Cities and uncheck the Use Current Directory checkbox.
5. Click the Select Directory button and navigate to the folder where the tl_2013_13_concity.dbf file is located. Select OK to save the directory path. below screen shot shows the ODBC dBASE Setup dialog (in this case, the .dbf file is located at the root of the 

6. Select OK in the ODBC dBASE Setup dialog and OK in the ODBC Data Sources Administrator to save the US Cities DBF reference.
7. Create a new package in SSIS and a new Data Flow.
8. Drag an ODBC Source adapter from the Toolbox into the Data Flow workspace and double-click the ODBC Source to open its editor.

Frequently Asked SSIS Interview Questions & Answers

9. In the ODBC Source Editor, click the New button next to the ODBC Connection Manager window.
10. Select the New button again when the Configure ODBC Connection Manager dialog opens.
11. The Connection Manager dialog enables you to reference the DBF file through an ODBC connection. Select US_Cities from the list, as shown in below screen shot.

12. Select OK in the Connection Manager dialog, and OK in the Configure ODBC Connection Manager dialog, which will return you to the ODBC Source Editor with the US_Cities connection selected.
13. In the “Name of the table or the view” dropdown list, choose the tl_2013_13_concity table in the list. below screen shot shows the ODBC Source Editor dialog

14.Click the Columns property page tab to bring up a list of the columns available in this file.
15. Select OK to save the changes of the ODBC Source adapter.
16. To demonstrate loading this ODBC Source to a destination table, drag an OLE DB Destination adapter and connect the blue data path from the ODBC Source adapter to the OLE DB Destination adapter.
17. Configure the OLE DB Destination to load the data to a new table in one of the sample databases.
After you run this new package, use SSMS to open the table you just loaded and observe the loaded results.

If you have a need to load data to an ODBC Destination, the process is very similar, but you use the ODBC Destination adapter to perform this operation.

List of Related Microsoft Certification Courses: