Connecting to MySQL and Writing Data to a CSV File Using ZappySys CSV Destination in SSIS

SSIS PowerPack CSV
1

Introduction

This article provides a comprehensive guide on connecting to a MySQL database within SQL Server Integration Services (SSIS) and writing the retrieved data to a CSV file using the ZappySys CSV Destination. By following these steps, you can efficiently manage and export data from your MySQL database to a CSV file, leveraging the robust features of ZappySys tools.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from the Customer Download Area or get the trial version.
  • ODBC tool: Installed on your system to connect to MySQL.
  • MySQL database: Include MySQL Connector/ODBC to establish the connection.

Steps

Create MySQL Connection

  1. Open the ODBC Data Source by typing “ODBC” in the search box and launching it.
    Open ODBC

  2. Create a User Data Source (User DSN) and select the MySQL ODBC Unicode Driver.

  3. Fill in the fields, including name, server, and credentials. Test the connection and click OK to save the configuration.
    Note: Use SET sql_mode='ANSI_QUOTES' in the Initial Statement to avoid issues in SSIS.

    MySQL connection with ODBC
    MySQL connection with ODBC573×669 30.9 KB

Connect with SSIS

  1. Add a Data Flow task to your SSIS package.

  2. Inside the Data Flow, add an ODBC Source component.

  3. Configure a new connection within the ODBC Source by selecting the MySQL driver connector and testing it.

    ODBC connection in SSIS
    ODBC connection in SSIS714×594 19.4 KB

  4. Select a table or use a query mode and preview the data. Click OK to save it.

    MySQL example
    MySQL example755×639 26.5 KB

  5. Now create a File connection for a CSV file.
    CSV file connection

  6. Drag and drop the CSV Destination and connect it with the ODBC Source.

  7. Inside the CSV Destination, in the Connection Managers, select the connection for the CSV file inside the CSV Destination

    CSV Destination File connection
    CSV Destination File connection723×346 9.87 KB

  8. In the Components Properties tab, configure settings such as overwrite file, Row/Column delimiter, and more.

  9. In the Input Columns tab, select the columns you want for the CSV file and click OK to save the configuration.

    CSV Destination Column input
    CSV Destination Column input723×664 17.6 KB

  10. Run the package with a destination component.
    Example

Conclusion

Following these steps, you can seamlessly connect to MySQL within SSIS and save the data inside a CSV file, leveraging the ZappySys tool for efficient data retrieval and integration. For assistance or inquiries, contact our support team via chat on our website.

References

For more information, refer to the CSV Destination documentation.