Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Export MySQL data to Excel using SQL Server Integration Services


By:   |   Last Updated: 2018-09-20   |   Comments (1)   |   Related Tips: 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:

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.


Last Updated: 2018-09-20


next webcast button


next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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?   


Learn more about SQL Server tools