Chapter 11Incremental Loads in SSIS

  • Using the Control Table pattern for incrementally loading data
  • Working with Change Data Capture

You can find the code downloads for this chapter at on the Download Code tab.

So far, most of the data loading procedures that have been explained in this book have done a full load or a truncate and load. While this is fine for smaller amounts of rows, it would be unfeasible to do with millions of rows. In this chapter, you’re going to learn how to take the knowledge you’ve gained and apply the practices to an incremental load of data.

The first pattern will be a control table pattern. In this pattern, you’ll use a table to determine when the last load of the data was. Then the package will determine which rows to load based on the last load date. The other alternative used in this chapter is a Change Data Capture (CDC) pattern. This pattern will require that you have Enterprise Edition of SQL Server and will automatically identify the rows to be transferred based on a given date.


The most conventional incremental load pattern is the control table pattern. The pattern uses a table that the developer creates to store operational data about the last load. A sample table looks like this:

CREATE TABLE [dbo].[ControlTable](
   [SourceTable] [varchar](50) NOT NULL,
   [LastLoadID] [int] NOT NULL,
   [LastLoadDate] [datetime] ...

Get Professional Microsoft SQL Server 2014 Integration Services now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.