By: Nisarg Upadhyay | Comments (1) | Related: More > Integration Services Excel
Problem
In this article, I am going to explain that how to export data from a MySQL database to an Excel file using SQL Server Integration Services.
Solution
Using data flow component of the SQL Server Data Tools, we can transfer data between various databases by configuring ODBC drivers. Open database connectivity (ODBC) is an interface, provided by Microsoft to connect applications to access data from databases. ODBC drivers use the ODBC interface to connect the application with a RDBMS.
Configure MySQL ODBC Driver
To connect the MySQL database server, we must create an ODBC data source. To download the MySQL drivers, visit MySQL product website and download ODBC driver for MySQL. See the below image as a reference:
SQL Server data tools is a 32-bit application and run in 32-bit mode so download the 32bit ODBC driver. See below image for reference.
If the correct drivers are not installed, you will get “The specified DSN contains an architecture mismatch between Driver and Application” error. See the following image:
Once the appropriate drivers are downloaded, install by running setup.
Once the drivers are installed, we must configure the ODBC data source to connect to a MySQL database. To do that, open the ODBC Data sources (32-bit). It is located at Control Panel > Administrative Tools > ODBC Data Sources. See the below image as a reference:
Open ODBC Data sources (32-bit). A dialog box “ODBC Data Source Administrator (32-Bit)” opens. In that dialog box click on Add. Another dialog box “Create New Data Source” opens which has all ODBC drivers installed on the computer. From that list, select “MySQL ODBC 5.3 ANSI Driver” and click on finish. See the following image:
After you click on “Finish”, a dialog box “MySQL Connector / ODBC Data Source configuration” will load to configure the MySQL connectivity parameters. In the dialog box, provide the configuration parameters to connect to the database. In data source name and description provide the appropriate name and description to recognize the data source. In the TCP/IP server text box provide the name of hostname or IP address of computer/server where MySQL is installed. Provide the appropriate username and password to login to the server and select the database. See the following image for reference:
I have installed MySQL on my local machine hence I have provided “localhost” as TCP/IP server text box. I am connecting to the server by “root” user hence the user is “root.” Root is not preferable, instead of root create another user and use it to authenticate to the MySQL database. I have created the WideWorldImportors database on MySQL database server hence I have provided WideWorldImportors in the database drop-down box.
Configure Excel Destination in SSIS
After setting the MySQL connection parameters, configure the Excel connection. To do that, double-click on the Excel destination. Once the Excel destination opens, click on New. Another dialog box opens. The Excel File Path is the location where the Excel file has been created. Select the version of the Excel file from the “Excel version” drop-down box and click OK. See the following image:
Configure Data Flow Task in SSIS
Once the source and destinations are configured, configure the Data Flow Task. To do that, drag a “Data Flow Task” from the SSIS toolbox and drop it into the Control Flow window of the SSIS package. Double click the data flow task and rename it “MySQL to Excel”. In the data flow, drag and drop the ODBC source and Excel destination from the SSIS Toolbox. Rename the ODBC source “MySQL Data Source” and rename the Excel destination “Employee Data” as shown in the following image:
Double click the ODBC Source (MySQL Data Source). A dialog box “ODBC Source” opens. In the “ODBC Connection manager” drop-down box, select “MySQL Destination.root” and click OK. In data access mode select “SQL Command,” in SQL Command Text window, write the below query and click OK.
select * from Employees order by jobtitle desc
See the following image:
Once the ODBC connection is configured, drag the blue arrow beneath “MySQL Data Source” and drop on the “Employee Data” Excel destination. See the following image.
Double click the Excel destination (Employee Data). A dialog box “Excel Destination Editor” opens. In the “Excel Connection manager” drop-down box, select “Excel Connection Manager” and click OK. For the data access mode select “Table Or view,” Select “Sheet1$” in the name of the Excel sheet drop down box. See the following image.
Click on “Mappings” to map the columns. The column name of the Excel and SQL table are the same, hence SSIS will automatically map them. Once the mapping is complete, click OK to close the dialog box. See the following image:
Run the Package SSIS Package to Export MySQL Data to Excel
Once the above steps are complete, the entire package looks like the following image. Click the Start button to run the package.
Once execution completes successfully, the data will be stored in the Excel file. See the following image:
Next Steps
- Try this out for yourself to learn how to use ODBC data sources as well as how to install additional ODBC drivers to expand the use of your SSIS packages.
- Check out the Excel related tips.
- Check out the SQL Server Integration Services resources.
About the author
Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
