Deleting or not deleting rows in ssis package destination table

Question

i used the import/export wizard in sql server 2005 management studio to export rows from an excel sheet to an sql table and checked the Do not delete rows in destination table option. I saved the export operation as a ssis package, and yes new rows are being appended to existing ones, but now i have a requirement to delete all rows in the destination sql table.

When i go into BIDS to edit my package, i cant find the option to change this behavior any where. does any one know how to change this setting the ssis package designer??

Answer 1

When you enable the delete destination rows option, a "Execute SQL Task" is added to the package's Control Flow. This task is used to execute a TRUNCATE TABLE statement on the destination. In your package, the control flow probably contains a single Data Flow Task. Just add an Execute SQL Task and connect it to the data flow task. Assign the destination connection manager to the Execute SQL Task and set the SQL statement to "TRUNCATE TABLE [yourdestinationtable]"

Answer 2

thank you very much Sven... Saved me loads of time... I was having the same trouble