Some time ago I was engaged in building Data Warehouse (DWH) for NAV database. To develop incremental data transfer I used system field timestamp, which is present in every NAV table. I build the table, called Timestamp Settings in DWH with the following structure:
Where CompanyID is the index of NAV company in DWH.
When the SSIS package launches, the script task, which reades the last timestamp values, is fired.
Script task specification is written in VB language. It reads values from Timestamp Settings table and stores them in global variables (variables with names like Last*Stamp):
For incremental data transfer from NAV to DWH I developed the template for data transfer task. Let's see for example data transfer for Salespersons table:
The main idea is to proceed only those records, which timestamp is greater then the last one. For this purpose I used SQL instructions, saved in local variable:
The value of variable is used for query expression in data source.
After proceeding record the event for updating last timestamp is fired. The script is very simple:
Finally, after all tasks been executed, the script for saving last timestamps is used:
The code of the script is simillar to loading timestamp values:
That's all. I'm new to SSIS, so, your feedback is highly appreciated.
When the SSIS package launches, the script task, which reades the last timestamp values, is fired.
Script task specification is written in VB language. It reads values from Timestamp Settings table and stores them in global variables (variables with names like Last*Stamp):
For incremental data transfer from NAV to DWH I developed the template for data transfer task. Let's see for example data transfer for Salespersons table:
After proceeding record the event for updating last timestamp is fired. The script is very simple:
No comments:
Post a Comment