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
Last activity: Feb 28, 2025, 01:21 PM