Guide: Configuring ODBC Driver in Azure Data Factory

Guide: Configuring ODBC Driver in Azure Data Factory

This guide walks you through the steps of setting up and using an ODBC driver in Azure Data Factory (ADF) for seamless data integration. By following this guide, you will be able to configure an ODBC driver, create a Linked Service, and define an ODBC dataset to efficiently transfer data.

1. Install and Configure the ODBC Driver

Before integrating with Azure Data Factory, ensure that the ODBC driver is installed and properly configured. The following steps will guide you through this process.

Steps:

1. Install the 64-bit ODBC Driver
Ensure that you install the 64-bit ODBC driver that supports your specific database. 

2. Open ODBC Data Source Administrator (64-bit)
On the machine where the Self-hosted Integration Runtime (SHIR) is installed, open the ODBC Data Source Administrator tool (64-bit).

3. Navigate to the System DSN Tab
In the ODBC Data Source Administrator window, go to the System DSN tab and click on Add to add a new data source.

4. Select the ODBC Driver
From the list of available drivers, select the appropriate ODBC driver for the database you want to connect to.

5. Configure the Connection Settings
Enter the necessary connection details.

6. Test the Connection
Click on Test Connection to verify that the connection settings are correct and the system can successfully connect to the database.

2. Create an ODBC Linked Service in Azure Data Factory

After configuring the ODBC driver, the next step is to configure the ODBC Linked Service in Azure Data Factory, which connects ADF to the database through the ODBC driver.

Steps:

1. Open Azure Data Factory
Go to the Azure Data Factory portal and navigate to Manage → Linked Services → New.

2. Select the ODBC Connector
In the search bar, type ODBC and select the ODBC connector from the list of available connectors.

3. Configure the Connection
You will now need to configure the connection settings:
  1. DSN Name: Enter the exact DSN that you configured in the ODBC Data Source Administrator.
  2. Connection String: Alternatively, you can use a direct connection string. For example:
Driver={SQL Server};Server=myserver.database.windows.net;Database=TestDB; 

Or use the DSN Name (e.g., DSN=QuickBooks).
Picture 1, Зображення

4. Test the Connection
Click on Test Connection to validate the connection between Azure Data Factory and the ODBC data source.

5. Create the Linked Service
Once the connection is validated, click Create to save the Linked Service.

3. Create an ODBC Dataset in Azure Data Factory

Now that the Linked Service is created, you need to define the dataset in Azure Data Factory. The dataset specifies the source and target data for your integration.

Steps:

1. Open Data Factory Studio
Go to Data Factory Studio → Author → Datasets.

2. Create a New Dataset
Click on New Dataset, and select ODBC as the source type.

3. Select the ODBC Linked Service
In the configuration window, select the ODBC Linked Service you created in the previous step.
Picture 1, Зображення

4. Configure the Dataset
  1. Table Name: Select a table or provide a SQL query.
  2. Query Option: You can also use an SQL query instead of selecting a table.
5. Save the Dataset
Once you have configured the dataset, click Save to complete the process.

With these steps, you've successfully configured an ODBC driver in Azure Data Factory and created an ODBC dataset for data integration tasks.

    • Related Articles

    • Setting Up MongoDB Atlas and Connecting via Devart ODBC Driver

      This article will guide you through the process of setting up MongoDB Atlas, creating a cluster, configuring access, obtaining the connection string, and connecting to MongoDB Atlas using the Devart ODBC Driver for MongoDB. 1. Creating a Cluster in ...

    • Setting up a Connection to ODBC in Delphi Using Devart ODBC Driver

      This guide will walk you through the steps of setting up a connection to an ODBC driver in Delphi. We'll use ODBC Driver for Salesforce as an example. Prerequisites: Devart ODBC Driver for Salesforce installed on your machine. A Salesforce account ...

    • Error When Creating a Linked Server with ODBC Driver: "Data source name not found"

      The error "Data source name not found" typically indicates that Microsoft SQL Server is unable to locate or access the specified ODBC data source. To resolve this issue, make sure that: The ODBC driver is correctly installed on the same machine where ...

    • How to Configure ODBC in Adobe ColdFusion

      This guide explains how to configure ODBC (Open Database Connectivity) in Adobe ColdFusion to enable seamless connectivity between ColdFusion and various databases. While ColdFusion primarily supports JDBC, it is still possible to use ODBC through ...

    • Setting up a Connection to Power BI Online Using Devart ODBC Driver

      In this guide, we will walk you through the process of setting up a connection to Power BI Online using the Devart ODBC Driver. This involves creating a DSN connection, setting up an on-premises gateway, and verifying Power BI report settings. Step ...