Truncate tables?

Question

I'm trying to create an Integration Service project that will use a set of existing (legacy) databases to create a new database. As I'm a SSIS noob, my progress is rather incremental and adding a truncate on the entire target database would help me test it more easily.

Is such a thing possible?

Answer 1

Here is an interesting solution

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Answer 2

This SQL statement will do it for you. If you want it to be part of an IS project then make it the SQL of a SQL Script task.

declare @object_id int
declare @str varchar(255)

select @object_id = min(object_id) 
from sys.tables
where name <> 'sysdtslog90'

while @object_id is not null
begin
    select @str = 'truncate table ' + name
    from sys.tables
    where object_id = @object_id

    print @str
    exec (@str)

    select @object_id = min(object_id) 
    from sys.tables
    where object_id > @object_id
    and name <> 'sysdtslog90'
end

If you are not happy with using the sys.tables view, then you could always define the table list yourself.

Answer 3

Why use such a complicated SQL statements? Why not just use a Execute SQL Task with an expression variable with value of (DELETE FROM tablename WHERE ControlKey = ?)