We have recently upgraded the SQL Server and SSIS projects from 2012 to 2019 version. Since this upgrade, one SSIS project is taking very huge time for completion. This SSIS project has a package which loads data from MySQL ODBC source and insert it into another MySQL ODBC destination via data flow task. The data is inserted in batches of 100K rows. Each batch previously took around 30 seconds but now it is taking 5 minutes.
I have tried out below things but nothing fixed the issue:
- Used different versions of MySQL ODBC drivers i.e. MySQL ODBC 5.3 Unicode Driver and MySQL ODBC 8.0 Unicode Driver
- Recreated the data flow task
- Added new ODBC connections in SSIS package and used them into data flow task