I troubleshooted a scenario where one of our overnight packages brought roughly 1/3 of all available rows. The package has been completed successfully, no errors, no warnings. Everything is fine, but less data in staging than we have in the source.
Everything looks OK on both environments. No recent changes to the ETL or data source. It works every night for the last couple years, and we never had issues. The only change we made to Azure DB recently, we added auto-scaling, because sometimes it struggles to cope with an increase in demand. I see that the auto-scale event has happened at about the same time as an SSIS package got executed. The package has been completed successfully, but the next one has failed.
To test this, I set the database to 200 DTUs and ran a test package. The package copies 80,000 rows from an archive table using complex conditions. The archive table uses COLUMNSTORE_ARCHIVE compression, so any query to this table is too difficult for 200 DTUs. The package triggered an auto-scale. And the package has been successfully completed after moving 54,000 rows. No errors, all green ticks on the SSIS tasks!
I disabled auto-scaling, re-ran the package, and all rows were copied.
Nice.
Is it a known bug? How to handle this scenario properly?
Auto-scaling is disabled now, but we want it back.
We use OLE DB to connect to source/destination.