Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Section 5

Common ETL Scenarios

• Lesson 36: Loading Data Incrementally
• Lesson 37: Using the CDC Components in SSIS
• Lesson 38: Using Data Quality Services
• Lesson 39: Using the DQS Cleansing Transform
• Lesson 40: Creating a Master Package

Chapter 36

Loading Data Incrementally

Importing an entire table each time you run a package is the simplest way to work, and creating a package to do this is easy. Replacing the whole table is sometimes called a wipe and load. However, if the table is very large, your package may run for a long time. Instead of replacing the entire table, you can find the changes in the table and make the same changes in the destination table. This is called an incremental load. Generally, an incremental load runs much more quickly than a complete load. This is especially true if your source table is very large and few rows are changed each day. Incremental load packages generally handle inserts and updates from the source table. Rows that have been added to the source table will be inserted into the destination table. Rows that have been changed in the source table will be updated in the destination table. What will you do in the destination when rows are deleted from the source? You may choose to delete them in the destination, set a deleted flag to True but not delete the row, or you may choose to ignore source deletes. In a data warehouse dimension load, it is common to ignore deletes.

How will your package know which rows have been inserted, updated, or deleted ...