Good question, and coincidentally one that I was asked yesterday by a colleague upon reading the article.

You are correct, nested transactions are not supported. This would be an interesting feature request for future versions althought I would fear that this would impact the inherent simplicity of the current behaviour.

Perhaps this could be handled via a new setting for TransactionOption, 'SupportedNested' or something similar.

My requirements are that I want separate transaction for each Sequence A, B, C, etc... If Sequence A transaction fails, the other should still be continuing with another transaction.

But I am getting an error regarding the OLEDB Error in next Task (e.g in Certain_Field_Value = 'B') "Lookup Data in SQL DB with values from Oracle ", the error message is ".......Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. ".

Nested transactions are complex and "dangerous". There is a raw limit of 32 on the maximum nested level (on both SQL Server 2000 and 2005) which you can hit if you are not careful. Triggers complicate this things further and therefore the logic on the client code gets really convoluted.

I beleive those reasons should be enough for keeping you from getting into the nested transactions world. I have been there and it is really ugly. Most non trivial applications employ the single transaction model for speed and clarity.

I am thinking that SSIS DTC does not really work or that there are some special attributesthat tasks contained in a sequence with transaction=required, that these tasks need tohave. Is sequentiality and same-thread guaranteed for all tasks in a sequence? I'm gettingfailures on the first task in a transactioned sequence for no reason, am tempted to bagSSIS/DTC and go with the literal BEGIN TRANSACTION ( which would involve redesigningthe whole loop -- arrgghh )