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:
A dialog box MySQL Connector/ODBC Data Source Configuration opens. In the dialog box, provide the following information.
- Data Source Name: The desired data source name that can be used by the SSIS package to establish the connection between source and destination
- TCP/IP Server: Provide the name of the MySQL Server that is hosted on Azure
- Port: Valid port number
- Username and Password: Enter the username and password to authenticate to the MySQL Server
- 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:
The values in the MySQL ODBC Data Source Configuration looks like the following image:
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.
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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.
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.
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:
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:
Open the text file to verify that data has been copied. The following is the screenshot of the 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.
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022