SQL Server 2019 – archive remote DB daily with archive date in each table

I am very new to SQL and was hoping for some help.

I have a database that is hosted remotely and I want to take a snapshot of all the views and archive them in a local 2019 database daily with an ‘archived’ date field in each of the local tables.

So far I have managed to set up an SSIS package to export the data to the local DB, which has copied the data to the new DB fine, but I want the archive process to add the new data to each of the tables with today’s date.

So I will be left with the same tables in the new DB but will have all historical data inside each of them.

Any help would be so gratefully received, or even just a nudge in the right direction would be great.

Kind Regards,

Sam

Answer

You can add a derived column step in your data flow:

And use GetDate / GetUtcDate / or a date variable