How to keep the data warehouse synchronised with the source systems? I'm a DBA who is responsible for exporting data from an ERP system for Business Intelligence purposes. The data from ERP system is being sent to the data warehouse and they are used in the ETL process.

We used to run the export process at the end of the day. The queries which collect the data used the WHERE clause with a document creation/modification date = yesterday.

This mostly works just fine - there isn't too much data to export from the source system (less resource consuming on both source and destination systems).

But some source tables don't have the modification date column (only the create date). 

At the moment I can see only 2 possibilities:


exporting all the records from the source system (very costly and impossible to mainain in a long run)
exporting records created in the last 7 days (less costly and pretty effective because older data is rarely modified)


I am looking for a better solution. I heard that SQL Server Integration Services have a MERGE JOIN and a LOOKUP transformations. I'm not sure if one of these provide the possibilities that I'm looking for.

What tool can you recommend? I'm sure it can be done efficiently in SSIS but I just don't know the right solution.
Change tracking offers the functionality you're looking for. When switched on, SQL Server creates additional internal tables.  Changes to your data tables are noted in these internal tables.  System functions allow you to pull changes out of these internal tables and transfer only changed rows.

A related technology - Change Data Capture - allows you to see the "before" and "after" values of changes.