In this article, I am going to explain ODBC drivers. We will go through the process to set up the ODBC driver for MySQL and use it to create an SSRS report. First, let me talk about ODBC drivers.
The ODBC, also known as Open DataBase Connectivity, is provided by Microsoft that allows us to connect the application to the SQL Server or other database servers like MySQL, Oracle, or PostgreSQL. Every database platform provides its setup of ODBC drivers that can be used to connect Windows or Linux applications to connect to the database.
When we install the MySQL server, the ODBC driver for MySQL is installed automatically. In the installation process, you can choose the different version of MySQL from the Select Products and Features screen. I have already installed it on my computer, so it is greyed out.
You can download the driver from here.
Once the driver is installed, let us configure the ODBC data source for MySQL. The Windows ODBC data sources (64-bit) is used to install and manage the ODBC drivers of various databases. We are going to use it. Open Control Panel Open Administrative Tools Open ODBC Data source (64-bit) See the following image:
A dialog box ODBC Data Source Administrator dialog box opens. On the User DSN tab, you can view the pre-configured ODBC data sources. We want to create a new System DSN; hence click on System DSN tab and click on the Add… button.
A dialog box Create New Data Source opens. In the list, you can see all see the list of various ODBC drivers that are installed on the computer. We want to set up MySQL ODBC Data source, hence choose MySQL ODBC 8.0 ANSI Driver or MySQL ODBC 8.0 Unicode Driver and click on Finish.
A dialog box MySQL Connector/ODBC Data Source configuration opens. In the Data Source name and description text box, provide the desired name and the description of the Data source.
If you are using TCP/IP protocol to connect to MySQL, then click on TCP/IP Server and provide the name and the port number of MySQL Server. If you are using Named Pipe protocol, then select Named Pipe and enter the appropriate value in the text box.
Enter the username and password in the User and Password text box to authenticate to the server. Select the desired database name from Databases drop-down box. Click OK to save the ODBC connection properties and close the dialog box. See the following image:
On ODBC Data Source Administrator (64-bit) screen, you can see that the MySQL for SSRS has been created. Click OK to close the dialog box. See the following image:
Once the ODBC Data Source is configured, let us create an SSRS report to test it. To create an SSRS report, you must download SQL Server data tools from this location. Once it is downloaded and installed, open it and click on File Hover on New Click on New Project. See the following image:
#mysql #server management #utilities
