Export MySQL data to Excel using SQL Server Integration Services

By:   |   Comments (1)   |   Related: More > Integration Services Excel


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.


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:

ODBC connector

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.

visual studio 2015 option

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:

connection manager error

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:

odbc data sources

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:

add new data source

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:

mysql data source

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:

excel data source

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:

ssis package flow

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:

odbc source

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.

ssis package flow

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.

excel destination editor

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:

excel destination editor mapping

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.

ssis package flow

Once execution completes successfully, the data will be stored in the Excel file. See the following image:

ssis package execution
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Nisarg Upadhyay 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

Comments For This Article

Thursday, February 21, 2019 - 11:16:20 PM - Randy L. Smith Back To Top (79087)

You took a big leap from setting up the ODBC connection to the Excel connection. How did you get to that Excel setup box?  Where is it in SSIS?   

get free sql tips
agree to terms