Business Intelligence Markup Language - SQL Server Integration Services Design Patterns - page 455

,ColC int NULL)
Use SSISIncrementalLoad_Source
Go
-- insert an "unchanged", a "changed", and a "new" row
INSERT INTO dbo.tblSource
(ColID,ColA,ColB,ColC)
VALUES
(0, 'A', '1/1/2007 12:01 AM', -1),
(1, 'B', '1/1/2007 12:02 AM', -2),
(2, 'N', '1/1/2007 12:03 AM', -3)
Use SSISIncrementalLoad_Dest
Go
-- insert a "changed" and an "unchanged" row
INSERT INTO dbo.tblDest
(ColID,ColA,ColB,ColC)
VALUES
(0, 'A', '1/1/2007 12:01 AM', -1),
(1, 'C', '1/1/2007 12:02 AM', -2)
The T-SQL statements in Listing 19-5 create two databases; SSISIncrement-
alLoad_Source and SSISIncrementalLoad_Dest . A table named
tblSource is created in the SSISIncrementalLoad_Source database and is
populated with three rows. Another table named tblDest is created in the SSISIn-
crementalLoad_Dest database and is populated with two rows.
The configuration created by Listing 19-5 is a basic setup for an incremental load.
ColID is the business key. This value should never change and should also uniquely
identify the row in the Source and Destination systems. The character values in ColA
of the Source and Destination tables indicate clues to the type of row. The A row is
present and identical in both the Source and Destination tables. It is an Unchanged row.
The row with a ColID value of 1 contains the ColA value B in the Source and the
ColA value C in the Destination table. This row has Changed in the Source since it
was initially loaded into the Destination table. The row with a ColID value of 2 exists
only in the Source. It is a New row.