mssqltips logo

Steps to Import MariaDB Objects and Data into SQL Server

By:   |   Updated: 2014-12-19   |   Comments (3)   |   Related: More > Integration Services Development

Cleaning, Validating and Enhancing the SQL Server Data Warehouse Contact Dimension

Free MSSQLTips Webinar: Cleaning, Validating and Enhancing the SQL Server Data Warehouse Contact Dimension

In this webcast we will demonstrate an ETL implementation for a Contact dimension in a typical data warehouse using Microsoft SQL Server Integration Services (SSIS) and the Melissa SSIS components. Join us and learn how to resolve common data quality challenges for SQL Server data warehouses.


Problem

MariaDB is a derivative of MySQL with the intent to keep this open source as well as compatible with MySQL. Sometimes it is necessary to import objects and data from MariaDB to SQL Server. In this example, we will work through the steps in SQL Server Integration Services (SSIS) to import the table structure and the data.

Solution

Here are the basic requirements to build the solution for this tip:

  1. SQL Server 2005 or later. In this tip, I am using SQL Server 2014.
  2. MariaDB should be installed. In this tip, I installed MariaDB and SQL Server on the same machine. You can find the MariaDB installer here.
  3. In the MariaDB installer, you will find the HeidiSQL which is a great tool to visually create database objects and administer the MariaDB database.
  4. An ODBC connector for MariaDB is necessary. You can get it here.
  5.  SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS) is installed.

MariaDB Object and Data Import with SQL Server Integration Services

In order to start, we are going to start the MariaDB database. This section is for newbies to the MariaDB world. I am assuming that you just installed MariaDB and HeidiSQL, but you have not used it.

  1. Start HeidiSQL.
    Tabular properties

  2. In the Session manager, select the option new session in root folder.
    Deploy tabular project

  3. Specify the root password (when you install MariaDB you will be asked the password). If it is a local machine, it is OK to use the Hostname 127.0.0.1. The port by default is 3306. In this example, we will work with the mysql database. Press the Open Window.
    Deploy success message

  4. In the mysql database, right click and select Create new>Table option.
    Connect to Tabular model

  5. We will create a table named test and add a column named id.
    Tabular database

  6. Move to the Data Tab and add some rows of data to the table.
    Connect to Tabular model

  7. Once you have created a MariaDB table with data, open SQL Server Data Tools (SSDT) and create a new SQL Server Integration Project. Drag and drop the Data Flow Task to the design pane.
    Open sql powershell

  8. Double click the Data flow task and drag and drop the ODBC Source and the SQL Server Destination join both tasks with a precedence constraint.
    Integration Service Project

  9. Before continuing, we need to configure the ODBC for the MariaDB database. Open the Data Sources (ODBC) on your Windows Server.
    show tabular tables

  10. Press the Add Button to add a User Data Source.
    process a dimension

  11. Select the Maria ODBC 1.0 Driver and press Finish.
    Second way to process a dimension

  12. Add a name to the connection and optionally you can add a description.
    Processing in progress

  13. In this example, we will connect using the TCP/IP protocol, the default port (3306), the root user with the respective password. The test DSN button is necessary to activate the Database drop down box with the databases available.
    Table properties

  14. You can specify the initial configurations of the connection and press Next.
    Last processed information

  15. You can specify the Miscellaneous Options of your preference and press Next.
    Last processed date updated

  16. The same for the cursor options. Once you have checked the options, press Finish.
    process another dimension

  17. Now you have just added a new User Data Source. Press OK.
    Process Database

  18. Return to the SSIS project created on step 9 and double click on the ODBC Source Task.
    Script action

  19. Press the New button at the right of the ODBC Connection manager combobox.
    xmla process script

  20. In the Configure ODBC Connection Manager, press New.
    Run a script

  21. Select the Mariadb connection created on steps 10-18. Optionally, you can test the connection with the test connection button and you will receive the Successful message.
    Process results

  22. Select a table name. In this example, the test table created on steps 5-7.
    Last processed information

  23. Now, click on the SQL Server Destination task.
    backup tabular database

  24. Press the New button at the right of the Connection Manager.
    Integration Service Project

  25. Press the new button.
    backup tabular file

  26. Select the name of the server and database where you want to store the imported table from the MariaDB database. In this example, we will select the AdventureWorks database.
    Integration Service Project

  27. In the "Use a table or view section", press the new button.
    Tabular roles

  28. Modify the table name to be test. The text should be like this:
    TAbular role members

  29. Now press the Mappings page.
    TAbular role members

  30. Make sure that the id columns from the MariaDB and the new table in SQL Server are mapped.
    TAbular role members

  31. Press Start in the SSDT to import the data.
    TAbular role members

  32. As you can see, 3 rows were imported. The rows imported are the three rows created in step 7.
    TAbular role members

  33. To verify the table, open the SSMS and in the database selected on step 30, verify the data and compare the results.
    TAbular role members

  34. As you can see, the data was imported successfully from MariaDB to SQL Server.
    TAbular role members

As you can see, you can import tables or views from MariaDB to SQL Server using SSIS. The ODBC Connector was still in beta version when this tip was published, so you may have some problems importing varchars and some special characters, but the MariaDB developers are working on these issues.

Next Steps
For more information, refer to the following links:

Last Updated: 2014-12-19


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

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.





Monday, December 22, 2014 - 9:36:28 AM - Jeremy Kadlec Back To Top

Everyone,

This has been updated.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, December 19, 2014 - 12:51:01 PM - Daniel Back To Top

Yes, it is a typo. Thanks a lot.


Friday, December 19, 2014 - 10:47:24 AM - Jerry Back To Top

I'm sure this was just a typo --- but I think it should be D S N rather than DNS.

Item 13:  In this example, we will connect using the TCP/IP protocol, the default port (3306), the root user with the respective password.

The test DNS button is necessary to activate the Database drop down box with the databases available.

 


download

























get free sql tips

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.



Learn more about SQL Server tools