0

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.

closed as too broad by Colin 't Hart, RLF, Max Vernon, RolandoMySQLDBA, Philᵀᴹ Apr 14 '15 at 17:48

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.