How to RollBack the transaction in SSIS Package....?? We are First Deleting the data from the 3 tables and then using three DFT we are Inserting the new Records from the CSV Files to the respective Destination table and after on completion of DFT we are checking if the ErrorExist Variable Value as Yes or No.If the DFT Fails it will set the ErrorExist variable value as Yes using the Script Task and goes in the Failure Section and do the RollBack. In our Development Server we don't have the option to Set the MSDTC Property So we are not able to Set the TRANSACTION PROPERTY AS REQUIRED. We also tried using the Begin transaction at the starting of the Delete Command but this will makes our DFT to go on a Loop,which I think Locking the table...So is there any way we can Rollback the transaction...?
MSDTC is required for SSIS transactions. You can still execute a SQL statement that includes it's own self-contained transactions, but you won't be able to wrap multiple package components in a single transaction without MSDTC.