Steps to Import MariaDB Objects and Data into SQL Server

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:

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *