Nisarg Upadhyay
Flat file destination

Configuring ODBC drivers for Azure Database for MySQL

September 15, 2020 by

In this article, I am going to show how we can configure the ODBC driver to connect the Azure Database for MySQL Server. Also, I am going to demonstrate the configuration using the SQL Server Integration Services (SSIS) package.

The SSIS package will copy the data of a table from the Azure database for MySQL to a text file. First, let us configure the ODBC driver to connect to the Azure MySQL database.

Configure ODBC driver to connect MySQL Database

In my previous article, Configure ODBC drivers for MySQL, I have explained how we can use ODBC Data Sources to configure the MySQL ODBC driver. Open ODBC Data Source Administrator (32-Bit) (Screen 1) Open System DSN Click on Add Select MySQL ODBC 8.0 UNICODE Driver (Screen 2) from the list and click Finish. See the following image:

Create New Data Source

A dialog box MySQL Connector/ODBC Data Source Configuration opens. In the dialog box, provide the following information.

  1. Data Source Name: The desired data source name that can be used by the SSIS package to establish the connection between source and destination
  2. TCP/IP Server: Provide the name of the MySQL Server that is hosted on Azure
  3. Port: Valid port number
  4. Username and Password: Enter the username and password to authenticate to the MySQL Server
  5. Default Database: Choose the database from the drop-down box

You can view the username and server name from the Overview page of the Azure Database for MySQL resource page. See the following image:

Azure database for MySQL

The values in the MySQL ODBC Data Source Configuration looks like the following image:

MySQL ODBC Data source  configuration

Click OK to close the dialog box. The Data Source Azure Database for MySQL has been created. You can see it under the System DNS tab. Click OK to close the dialog box.

ODBC Driver is configured

Our ODBC driver to connect to the Azure MySQL is configured. Now let us create an SSIS package to copy data from Azure MySQL Database to the text file.

Export data of a table from Azure Database for MySQL instance to a text file

As I mentioned, I have created a database named Video_Library on the Azure MySQL database. I have created a table named movies on it. We are going to copy the data of the table named movies to the text file. The text file named movies_table has been created on the desktop of my workstation.

To create an SSIS package, Open SQL Server data tool Click on File Hover on New Click on Project. See the following image:

New SSIS Project

On New Project dialog box, expand business intelligence Select Integration service Click on Integration Services Project. Provide the desired name in the Name textbox. See the following image:

New Project

To copy the data between source and destination, we must use the Data Flow Task; therefore, drag Data Flow Task and drop it on the Control Flow window. See the following image:

Drag and drop data flow task

To configure the source and destination, double click on the Data Flow Task. In the Data Flow window, drag and drop the ODBC Source and Flat file Destination from the SSIS toolbox. See the following image:

ODBC Source and Flat File Destination.

Provide the desired name to the source and destination tasks. First, let us configure the ODBC Source. Double click on it. A dialog box ODBC Source Editor (Screen 1) opens. In the dialog box, click on New. Another dialog box Configure ODBC Connection Manager (Screen 2) opens. Click on New. See the following image:

ODBC Connection manager

A dialog box Connection Manager opens. On the dialog box, select “Use user or system data source name”. Choose the name of the data source from the drop-down box and click OK to close the dialog box. See the following image:

Choose data source

On the Configure ODBC Connection Manager screen, you can see that the connection has been created. Click OK to close the window. See the following image:

Configure ODBC connection manager

On the ODBC Source Editor screen, select SQL Command from the Data access mode drop-down box. Enter the following query in the SQL command text.

select title,
release_year,rental_duration,rental_rate,length,replacement_cost,rating,special_features
from movies

Click OK to close the dialog box. See the following image:

ODBC Source editor

Now, let us configure the destination. Before we configure the Flat File destination, we must connect the source and destination. To do that, drag the blue arrow and drop it on a Flat File destination. See the following image:

Flat file destination

Double-click on Flat file destination. Flat file Destination Editor (Screen 1) opens. Click on New. In the Flat file Format dialog box (Screen 2), select Delimited file format and click OK. See the following image:

Flat file destination editor

On the Flat File Connection Manager Editor dialog box, enter the location of the text file in File name textbox. You can change the format of the file OR specify the Header row delimiter. Once the destination is configured, click OK to save the configuration and close the dialog box.

Flat file connection manager editor

Now let us perform the mapping of the column of the table and column of the text file. To do that, click on Mappings on Flat File Destination Editor dialog box.

Mapping the column

As you can see in the above image, the mapping was done automatically by SSIS. Click OK to close the dialog box. The SSIS package looks like the following image:

SSIS Package

Once the SSIS package is created, let us test it. To do that, click on the Execute button from the menu bar. Once the package is executed successfully, it should look like the following image:

Package executed successfully

Open the text file to verify that data has been copied. The following is the screenshot of the text file.

Text file

As you can see above, data has been copied correctly.

Summary

In this article, we have learned about the configuration process of the ODBC driver to connect to the Azure Database for MySQL. I have also demonstrated the use of the ODBC driver to create an SSIS package that populates the data from the table of the MySQL Azure database and copy it to a text file.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
Azure, Integration Services (SSIS), MySQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

51 Views