30

I use SSIS to generate and transform new data for later use in a new system. I have a problem every time I run my SSIS Package, it keeps inserting new records to my destination tables.

How can I empty my destination table (/OLE DB Destination) first and then inserting the newly generated records?

Currently the workaround for this problem is performing a delete from DestTable before running my package.

Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
Tassisto
  • 9,877
  • 28
  • 100
  • 157

4 Answers4

49

Put your delete statement in an Execute SQL Task. Then make it the first component of your flow. The component looks something like this:

enter image description here

TsSkTo
  • 1,412
  • 13
  • 25
  • 28
    `TRUNCATE TABLE ` is *much* better for tables with many rows. Not only will it be faster, it won't fill up the transaction log. I've had a production database server crash due to a transaction log filling up the disk after a `DELETE FROM` command was executed on a table with 30 million rows. – Dan Bechard Jan 07 '15 at 21:38
36

Create an Execute SQL task. Have it run first. For the sqlstatment do.

Truncate table DestTable

Using 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.

Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
  • you sure about this ? – vhadalgi Nov 27 '13 at 11:25
  • Its how i do it. Im sure there are a lot of diffrent ways of removeing data from stageing tables before reloading new information. Everyone has there own way of doing things. – Linda Lawton - DaImTo Nov 27 '13 at 11:27
  • 3
    +1 - I really don't care which one is better. But the explanation you gave about the speed is important. But I got the answer for my problem that's what is important. You can ask a question about this [has to be according to the Stack Overflow rules] – Tassisto Nov 27 '13 at 12:10
7

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.

MSDN Reference

WaitForPete
  • 417
  • 4
  • 5
0

you need to use this

truncate table table_name

this empties the table

vhadalgi
  • 7,027
  • 6
  • 38
  • 67
  • you can put this in exe-sql task,`delete from table` and `truncate table` does the same thing !! – vhadalgi Nov 27 '13 at 09:36
  • 8
    They really don't. They are *similar*, but nevertheless, different. Heres a link http://www.mssqltips.com/sqlservertip/1080/deleting-data-in-sql-server-with-truncate-vs-delete-commands/ – TsSkTo Nov 27 '13 at 09:42
  • thanks for **[link](http://www.mssqltips.com/sqlservertip/1080/deleting-data-in-sql-server-with-truncate-vs-delete-commands/)** – vhadalgi Nov 27 '13 at 09:54
  • 2
    Truncate table and delete table are not the same thing. They may do the same thing but that doesnt mean they are the same. http://codebetter.com/raymondlewallen/2005/05/09/the-difference-in-truncate-and-delete-in-sql-server/ Truncate table is faster and if you want to delete everthing in the table anyway why not do whats fastest. – Linda Lawton - DaImTo Nov 27 '13 at 11:31
  • i know they does same stuff but they are quite different !! – vhadalgi Nov 27 '13 at 11:33