Sql-server – SQL ssis delete imported rows from source table

sql serverssis

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

Best Answer

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.

control flow setup

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;

dataflow

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;

results

It works, it works!

Related Question