Hello everyone, I have a problem with a SSIS. I can not handle properly rollback if an error occurs. Let me explain better, attached you can see how I structured the DTS launch other packages. I wish that the "Main Container" create the transaction and the "Contenitore Sequenza" will inherit the same transaction. All the packages inside the two containers must be part of the same transaction created from the Main Container, with exception of the "Packet 3" that it is out. These are the parameters of IsolationLevel and TransactionOption that I configured: Main container: Serializable Required Package1: Serializable, Supported Package2: Serializable, Supported Pacchetto3: Serializable NotSupported Contenitore Sequenza: Serializable, Supported Pacchetto4: Serializable, Supported Pacchetto5: Serializable, Supported Pacchetto6: Serializable, Supported Pacchetto7: Serializable, Supported When I run the project everything is working correctly, but if I create an error such as in the package7 I expect that rollback all packages but not the package 3. Rollback work only for the packages that are within the "Contenitore Sequenza". The package 3 block the rollback. When he knows how to help me or advise me? Best Regards Antonio

Put all components in the diagram into one overarching container. Set the transaction option of that overarching container to REQUIRED Set Transaction Option of Package 3 to NOT SUPPORTED Set EVERY OTHER Transaction Option to SUPPORTED. Now, all will be in same transaction, except Package 3. Hope this helps.Peter Carter http://sqlserverdownanddirty.blogspot.com/

also remember to have MSDTC runninghttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

I just tried it but the process goes in an infinite loop in Package 3. The package 3 can't access resources because they are locked by the transaction.

Well if package 3 is attempting to access the same resources, then this will be a problem. Depending on what you are doing in the child packages, try lowering the isolation level, but that is standard transactional blocking/contention :-/ Basically, you are locking the resources with the transaction that begins in Main Container, and then trying to access them (whilst locked) from Package 3.Peter Carter http://sqlserverdownanddirty.blogspot.com/

Yes exactly. Package3 must access the resources involved in the transaction. So I setup the Package 3 NotSupported but change the isolation level. What kind of Isolation Level I can use ? "ReadUncommited" not work (same problem).

Well it depends what resources they are. If they are Database resources, then in theory ReadUncommitted should work (although it often doesn't) If they are file system/non-relational resources, then there is not much you can do, as even though SSIS technically supports it, the OS does not.Peter Carter http://sqlserverdownanddirty.blogspot.com/

Yes. Package 3 reads and writes data in the database. ReadUncommited indeed not work :(

Hi Antonio, If you are writing data in here, then changing the isolation level is not going to help. The isolation level only affect locking behaviour for read operations, not write operations. You are essentially trying to do 2 different operations at the same time. To my understanding, there is no way you can achieve what you are trying to do, without including Package 3 in the same transaction. Is there a reason you can no do this? If it is just a case of not wanting it rolled back, could you include some compensation code at the end of the transaction, that will redo the changes made my package 3 in the event of failure?Peter Carter http://sqlserverdownanddirty.blogspot.com/

At this point I should move the package 3 (who writes some warning) at the end of the transaction!!

I may be excluded from the rollback only certain tables?

Hi, You can not exclude certain tables from the rollback. This would leave the database in an inconsistant state, and SQL does not allow this. If business logic allows, I would write the warning at the end, after the transaction has finished. If it does not allow for this and you want to use compensation code then you would write the warning in task 3, then, store the warning in a variable, then after the transaction has finished, do a check to see if the transaction has been rolled back, and if it has, write the warning again... PetePeter Carter http://sqlserverdownanddirty.blogspot.com/