truncate tmp tables before or after dts package execution?

[Glowworm27]

Hello all,

When writing a DTS package where you use temp tables and such. Would it be better to truncate the tables before you begin the main part of the DTS package so they are empty and ready for use, or after you are done executing the package, so you don't leave junk data just laying around?

I would tend to truncate them prior to package execution so I know they are empty before I use them. Also in the dev phase I might want to look at them to see what has been going on in the dts?

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[mrdenny]

I truncate them as the first step in the package. That way I know that they are empty. If you truncate them at the end of the package someone could borrow your tables for something else and leave data in them which would them screw up your package.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[MDXer]

Also by truncating your tables as the first stemp your data remains in the temp tables allowing you to more easily troubleshoot if a problem arises.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dyarwood]

I have found that the truncate function can not be rolled back. ou could consider the drop and recreate method which could be rolled back.

 

[mrdenny]

Correct Truncate table is not a loggable command, which is why it can remove 1 billion records in less than a second. Dropping and then recreating tables is not the most efficient method of clearing the tables, and could easily leave your database file more fragmented than it needs to be.

Denny

--Anything is possible. All it takes is a little research. (Me)