O'Reilly logo

Professional Microsoft SQL Server 2014 Integration Services by Chris Rock, Mike Davis, Jessica M. Moss, Devin Knight, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 11Incremental Loads in SSIS

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

You can find the wrox.com code downloads for this chapter at http://www.wrox.com/go/prossis2014 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.

CONTROL TABLE PATTERN

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] ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required