Hi i have a package with a foreach loop container,the components inside it are 1)execute sql task 2)execute sql task 3)execute sql task 4)dataflow task 5)execute sql task 6)execute sql task the first 3 tasks use a same connection manager, the data flow task uses two different connections(source and destination) which in turn are different from the first 3 tasks,the last 2 tasks use entirely different connection managers. How can i achieve the Rollback ability in this scenario if any of the task fails,it should roll back all the previous transactions.(i know sequence container serves the purpose but we are not enabling the MSDTC service).is there anyway to do this with out involving DTC.

In short, no. MSDTC and other similar technologies like XA were designed for precisely the case you mention -- you have multiple things going on across multiple resources (like databases) and you'd like to treat the collection of of those things as an atomic unit of work. A close approximation you could achieve, albeit with some work, would be to implement a "compensating action" for each of the operations. So for example if step 3 fails, you immediately clean up steps 1 and 2 along with any partial work completed in step 3. Such a mechanism can be an acceptable substitute for real atomicity, in data warehousing or other scenarios where volumes of data are large, it's easy(ier) to discover partially completed work, and the likelihood and severity of failure is fairly small. Challenge exists here because implementing this correctly is non-trivial, especially if you want to handle a case such as "the power went off part way between step 4 and 5." Two-phase distributed transaction mechanisms like DTC are built for this kind of stuff. -David

Thanks for the reply David.I know it is impossible but this was my last try so i put it here.

Got to be v careful with MSDTC as it imposes scalability issues and the anti-patterns inherent with 'big transaction' patterns. If the destination database is a single node you can - Use SSIS steps to import your data into local staging tables - Once staging steps are complete, use a single T-SQL 'merge step' with a local (not distributed) transaction to do an atomic 'merge' into local tables. After commit you can truncate your staging tables. Without the distributed transaction, your overall system will be much more scalable. The cost is additional complexity / space. If you're using MSDTC at all you should read up on Brewer's theorem. Are you going to block readers on a remote node whilst you await a (lost) response from MSDTC? David mentions that compensating transactions may be preferable If you're using MSDTC, suggest you develop a suite of performance tests that emulate real world access (e.g. multiple users making concurrent requests). You might find a MSDTC based design performs very poorly under real world usage. (Don't use it!)