With the SQL Server 2000 DTS has very good capability of treating the huge processes as transactions, let's assume these parameters: DTS Package with "Use Transactions" and "Commit on successful package completion" boxes checked. No transaction related boxes checked in the Workflow Properties of each Package step. So the question is given these parameters, will SQL Server still treat the package as a single transaction, or will each qualified step (Execute SQL Task, etc.) be executed as its own autonomous...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/perftune/archive/2007/08/02/1229.aspx