SQL Server Performance Forum – Threads Archive

DTS Transaction question

We have many DTS packages–some with multiple operations in one step and I’m wondering if these are being "bundled" into one implicit transaction by the COM layer of DTS. Can anyone explain how this works? If you have a select into statement and a subsequent update statement on a different table but these two statements are in the same DTS step, does this constitute an "implicit transaction" and would the locks be held on the tables in question until the step completes?

---

Not unless you put explicitly declare a transaction to wrap the statements. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

FOr inside DTS refer tohttp://www.sqldts.com website for further information. (Moderator move it to DTS forum)
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

There is an "attempt to join existing transaction" option you can use on the steps in a package if you’ve set the package to run as one big transaction. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_tx_660j.asp

---

]]>