Sample Header Ad - 728x90

SSIS ODBC Destination to MariaDB is slow – How to enable bulk inserts?

0 votes
0 answers
86 views
I'm trying to copy data from SQL Server to MariaDB using SSIS. - I’ve configured a DSN using the official MariaDB ODBC connector. - My SSIS data flow consists of a simple source query (retrieving ~5,000 records) and an ODBC Destination. - However, the write performance is extremely slow. After enabling MariaDB's general log, I noticed that SSIS is generating one INSERT statement per row, significantly slowing down the process. I tested an external third-party component, which was much faster. From the logs, it appears to use a bulk insert approach, like this:
INSERT INTO tablename (col_1, col_2, col_3, col_4) 
VALUES 
    (7529766, NULL, 5, '2024-01-01 03:17:17'),
    (7529767, NULL, 5, '2024-01-01 03:17:29'),
    (7529768, NULL, 5, '2024-01-01 03:17:32')...
I’d like to achieve similar performance without relying on paid third-party components. Is there a way to configure SSIS's ODBC Destination to perform batch inserts instead of row-by-row inserts? Are there any MariaDB ODBC driver settings that can improve performance?
Asked by Mattia Nocerino (512 rep)
Feb 26, 2025, 09:04 AM
Last activity: Feb 28, 2025, 01:21 PM