3 year older data moving source to staging table and then loading to the final table after delete only moved data from source how to achive that in ssis package? This process should have happen daily.
I am using below query for select 3year old record
Select top 5000 column1,column2,column3,rec_date
from source_table
where (rec_date>DATEADD(year,-3,GETDATE()))
I create flow task for moving data but how to delete the moved records?
Assuming you have some kind of ID/primary key in your source table, I'd be tempted to do it like this:
Select the top 5000 IDs you want into a DataTable object (writing it to an object variable in SSIS) using a Script Destination.
In the next data flow, use the DataTable as a source (Script Source) and get the data you need from the source table using a Lookup against the ID column. This then goes into your staging database.
Next, use the IDs in the Datatable to delete the source records. There are many ways to do that, e.g., use a For Each container in SSIS (i.e. for each record in the DataTable), construct a single DELETE statement to execute against the source table, etc.
