SQL ssis delete imported rows from source table

Question

I have setup a simple SSIS package that copies data from a source table to a destination table which works. I would like to get the package to also delete the data in the source table that has been copied to the destination, there seems to be a great deal on deleting from the destination but not the source, how can I achieve deleting the copied data from the source? Thanks

SQL 2008 R2

Answer 1

Since you're working with SQL Server 2008, you can take delete first approach as I outlined on this SO answer https://stackoverflow.com/a/15508174/181965

A option, that might sound stupid but it works, is to delete first and use the OUTPUT clause.

I created a simple control flow that populates a table for me.

IF EXISTS
(
    SELECT 1 FROM sys.tables AS T WHERE T.name = 'DeleteFirst'
)
BEGIN
    DROP TABLE dbo.DeleteFirst;
END

CREATE TABLE dbo.DeleteFirst
(
    [name] sysname
);

INSERT INTO
    dbo.DeleteFirst
SELECT
    V.name
FROM
    master.dbo.spt_values V
WHERE
    V.name IS NOT NULL;

In my OLE DB Source, instead of using a SELECT, DELETE the data you want to go down the pipeline and OUTPUT the DELETED virtual table. Somethinng like

DELETE
    DF
OUTPUT
    DELETED.*
FROM
    dbo.DeleteFirst AS DF;

It works, it works!

Answer 2

I think it would be helpful to know why you are deleting the information from the source table as well as what kind of table it is(stage, etl, dim, etc..). It wouldn't help either if you specified whether you want to delete the table itself or just data in the table.

Since you specifically said "also delete the data in the source table" I will take it that you would like to keep the table itself. In which case if you add an 'Execute SQL task' and inside of that task you put a T-SQL statment to truncate the data in the source table you will delete the information source table data.

Now remember to add that Task after you have inserted into the destination table itself considering you referenced how you would like to delete source data after the move. below is T-SQL code to truncate and if you are not sure what truncate does look up the function on MSDN for reference(but essentially it deletes table data while keeping the table and the structure itself in place).

TRUNCATE TABLE schema.tablename

if you would like to delete the entire table the code below should work as well

DROP TABLE schema.tablename

Also this solution is only applicable if you are copying all the information from the entire source table to a destination table if you are taking only small specified sections such as only a couple columns or rows that you have identified then you will need to do the same identifications to remove those rows.

in which case you should look up documentation on DELETE and possibly CURSOR for use in deleting specific rows.