How to integrate data between SQL Server and MariaDB using SSIS

We all know about SSIS. Its a great tool to make a data integration between systems and folders. However, the only thing that is missing in SSIS is the real time data integration such as Kafka.

In this quick steps, we will send data from SQL Server to MariaDB using SSIS tool.

Setup and create the SSIS project

• Make sure that you have installed Visual studio 2019 with data tools option. Follow the instructions in this link SSIS Setup.
• Make sure to download and install the Integration Service.
• After all above, visual studio now is ready, right click on the visual studio 2019 icon and run it as an admin “Must be admin”.
• Create a new project.
• In the search text above write “Integration Services Project”, then finish the Wizard.

Download the MariaDB ODBC connector

Click on this link to download MariaDB connector MariaDB Connector and follow the steps below to download the right MariaDB Connector thats matching with your OS.

• Navigate to the connectors tab.
• From product select ODBC connector.
• In the OS select the operating system thats match your needs. (In my case, ive selected MS windows 32-bit)
• Download the connector setup file.

Setup the MariaDB connector

• Open the downloaded file and install it.
• In the search bar write ODBC data Sources (32-bit), and select it from the menu.
• In the “User DSN” click Add to create a new connection profile.
• Select the driver, in our case is MariaDB ODBC 3.1 Driver, then click finish.
• Give it a name “NewMariaDB” or any name else you want.
• Give the MariaDB server name or IP Address, the port, the username and password, then click on “Test DSN”.
• The connection must setup seccussfully.
• Select the Database you want to connect with from the drop down list, then finish the system till the end.
• Now the Connector is ready to be used in SSIS.

Table information between the two DB systems

In order to copy the data between the databases, you need to create a table in the destionation database in order to get all the upcoming records inside it. You have to be aware about the columns length, name and datatype. For example if the is a column name "ID" and datatype is "int" as a primary key, the same column with the same specifications must be locatedc in the destination database table.

SSIS ODBC Connector, SQL Server OLE DB Provider

• In the Visual studio, open the SSIS project you created back in the first step.
• In the left menu, drag and drop “Data Flow Task” to the working space.
• Double click on the “Data flow task”, after rename it if required.
• We will make a data integration from SQL Server to MariaDB, so our source is “Source Assistant”.
• A pop-up window will open to you to select the database engine you want to connect with, from the list select SQL server, then press on “New…” on the right.
• In the “Server or file name” field, write down the SQL server IP Address.
• In the “Login to the server” section, select the login type you want.
• From the “Initial catalog” drop down list, select the database name you want to connect with.
• Click “test connection” to validate the connection, the click on “OK”.
• Right click on the same “Source Assistant” and select edit.
• Select the table name you want to get the data from. you can write an SQL statement instead.
• Click on “Preview” button to see the data saple inside the selected table.
• Click on “OK”

Now the SQL Server connection is ready to fetch the records, now lets setup the destination of these records, is Maria DB ODBC.

• From the SSIS Toolbox, select ODBC Destination, drag and drop it to the work space, right click and select “Edit”.
• Click on “New” button to setup a new connection.
• Since we dont have a “Data connections” profile yet, we have to create a new on by click on “New” button.
• From the “Data source specification, select the profile name you created back in “Setup the MariaDB connector” step.
• Write the username and the password, test your connection then click “OK”, and also “OK”.
• Choose the “Data access mode” to be “Table Name-Batch”.
• Select the Table Name.
• Since we didnt match the arrow between the Source database and the destination one, click “OK”, go to the “OLE DB Source”, get the blue arrow to hit the “ODBC Destination”, then open the “ODBC Destination again, go to the “Mapping” to map each column from source table to the destionation one.
• Now everything looks good, right click in the working field and select “Execute task”.

Thats all

Mohammad Masadeh

Sr Data Engineer