Changed records since last export

Question

Im trying to figure out the best way to do delta exports using SSIS. I have a set of tables in a database that should be exported to a set of files at daily interval. I will have to export ONLY new and altered records. In each table there is a field called LastEdit that holds the date when the record was last edited (or created).

So my initial thought was to have a new table with something like "id","Jobname", "LastRun". Now whenever i export a table using a SSIS job, i will write the date of the run in this table along with the job name. And then next time i have to export data i will look in this table and find last run date and only export records newer than this.

Im fairly new to SSIS so im thinking if this is the best way or if there are some build in functionality for doing this, or if someone have a better design pattern for this.

Thanks in advance

Answer 1

SSIS + Change Data Capture is designed exactly for this.