How to empty my destination table before inserting new records in SSIS?
Put your delete statement in an Execute SQL Task. Then make it the first component of your flow. The component looks something like this:
Create an Execute SQL task. Have it run first. For the sqlstatment do.
Truncate table DestTableUsing truncate table is better then using delete as it ignores all the indexes and just removes everything.
For a little background info. I will try and explain why you should use truncate table instead of delete table. Delete table is a row based operation this means that each row is deleted. Truncate table is a data page operation the entire data page is delocated. If you have a table with a million rows it will be much faster to truncate the table then it would be to use a delete table statment.
One caveat about using Truncate table, it does run better for the reasons stated. However it also requires additional privileges for your SSIS System account.You should be sure that those are available to you in Production, otherwise you will have to use Delete.