I am trying to use SQL transaction with my SSIS package so if any error comes changes are rollback.
I am using an "Sequence Container" in Control Flow for this and setting its "Transaction Option" to "Required".
But it fails to execute the package and gives DTS error . (Everything is working fine if i remove the Data Flow from Sequence Container).
i have done the changes on server as described here: http://www.databasesupport.eu/categories/sql-server/1482-ssis-distributed-transactions
Could you please tell me if there is something i am missing. Do i need to do DTS settings on both the servers from where i am importing the data and to which i am importing.
Please tell what i am missing. I am very new to SSIS.