Truncate Table Statement And Transactions(using RetainSameConnection Property Of OledbDestinationConnection)

Hi



Can we have truncate table with in a transaction using SSIS package.

here is the scenario.

OledbDestinationConnection 's "retainsameconnection" property has been set to true.

I have added a Execute SQL Query task to "begin transaction".

second Execute SQL Query task to truncate a table. say table "A" . truncate table a(table does not exists in the database).

third to rollback a transactions if the second task fails.



Now when I am running my package, I am getting an error saying

The Rollback transaction has no corresponding "Begin transaction".

However if , I truncate a correct table , which exists in database, then all steps gets executed successfully.



And the truncaion also got rollbacked.Same goes with "Commit transaction" in the third steps(if a replace Rollback with Commit).



I don't understand this strange behavior.



Any clue is highly appreciated.



My real problem is I want to rollback a transaction, if Truncate table step fails.