Enable transactions in your SSIS package. You can either use the built-in transaction mechanism which uses MSDTC, or you can create your own transaction by putting a SQL Task with "BEGIN TRANSACTION" at the start of your package and a SQL Task with "COMMIT" at the end of the package. Make sure you set RetainSameConnection to true for the connection manager.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP