In this scenario, we're going to use DMT to consolidate data (i.e. transfer data) located in several systems into one destination database.
Let's say you have 3 different systems containing sales data:
Sales data needs to be consolidated into one SQL Server database. It needs to be collected every day and loaded into the GLOB_SALES table.
Note: For our example, we chose an Oracle, a Sybase and an SQL Server database but this would work the exact same way with any other database.
You can easily implement this scenario with DMT:
![]() |
e.g. |
SELECT * FROM SALES1 WHERE PROCESSED = 'N' |
e.g. |
SELECT * FROM SALES2 WHERE PROCESSED = 'N' |
Note 1: The data file field for both jobs is automatically populated as they belong to a data group.
Note 2: Scheduling parameters for both jobs are not required as they belong to a Schedule Group (i.e. they will run automatically when the group is run).
Note 3: For our example, we assume that both tables Sales1 and Sales2 have the same columns. But we could easily accommodate different table structures by writing specific SQL statements for each.
Once the export group and jobs are created, you can validate them to make sure they are error free and ready to run (right click on the Group then "Validate Group").
Active | Dest. Column (in dest. database) | Value (from src. data file) | Comments |
---|---|---|---|
Yes | SALE_ID | $DFC_[SALE_ID] | |
Yes | SALE_DATE | Convert(datetime, $DFC_[SALE_DT]) | Use SQL server Convert function for date field (Eval must be unchecked) |
Yes | CLIENT | $DFC_[CLIENT_ID] | |
Yes | PRODUCT | $DFC_[PROD_ID] | |
Yes | QTY | $DFC_[QTY] |
Once the Export and Load Groups/Jobs are created, you can let the scheduler run them automatically. Just go to the scheduler Tab and click the Start button.
When the scheduler is started:
Note: It's always a good idea to run groups or jobs manually at least once before scheduling them.