Hi i am currently trying to optimize an SSIS package when i do an Upsert/delete which handels about 93+ million rows from a table in a remote MariaDB source. The table also contains approximately 63 columns.
Currently i'm using Sort and Merge Join in my package but as i've read some guides its recommended to do the sorting part in the server and not with the sort functionality in SSIS DT, as it puts a load on the SSIS Server Memory.
And as i'm currently using this solution in Azure Data Factory running the package fails (most often Times out, even though i've increased the Time Out properties both in package side and in Azure Data factory).
What is the recommended way to tackle this?
If i've understood it right and as i mentioned it before i can skip the load on the SISS server by sorting DB-Server-Side. But as i'm new to the whole SQL and SSIS stuff i'm not quite sure how a sort like that would be in the SQL Command.
Also i've though about batching but even here i'm uncertain how that would work in SSIS.
What is recommended here?
My SSIS-Package looks like this right now:
I Followed this type of example: Synchronize Table Data Using a Merge Join in SSIS
(FYI: The red error icons are there because i lost connection during the screenshot, this is a fully working solution otherwise.)