How do you get an SSIS package to only insert new records when copying data between servers

Your example seems simple, looks like you are adding only new people, not looking for changed data in existing records. In this case, store the last ID in the DB.

CREATE TABLE dbo.LAST (RW int, LastID Int)
go
INSERT INTO dbo.LAST (RW, LastID) VALUES (1,0)

Now you can use this to insert the last ID of the row transferred.

UPDATE dbo.LAST SET LastID = @myLastID WHERE RW = 1

When selecting OLEDB source, set data access mode to SQL Command and use

DECLARE @Last int
SET @Last = (SELECT LastID FROM dbo.LAST WHERE RW = 1)
SELECT * FROM AlphaPeople WHERE ID > @Last;

Note, I do assume that you are using ID int IDENTITY for your PK.

If you have to monitor for data changes of existing records, then have the "last changed" column in every table, and store time of the last transfer.

A different technique would involve setting-up a linked server on Beta to Alpha and running your example without using SSIS. I would expect this to be way slower and more resource intensive than the SSIS solution.

 INSERT INTO dbo.BetaPeople
 SELECT * FROM [Alpha].[myDB].[dbo].[AlphaPeople]
 WHERE ID NOT IN (SELECT ID FROM dbo.BetaPeople)

Simplest method I have used is as follows:

  • Query Alpha in a Source task in a Dataflow and bring in records to the data flow.
  • Perform any needed Transformations.
  • Before writing to the Destination (Beta) perform a lookup matching the ID column from Alpha to those in Beta. On the first page of the Lookup Transformation editor, make sure you select "Redirect rows to no match output" from the dropdown list "Specify how to handle rows with now matching error"
  • Link the Lookup task to the Destination. This will give you a prompt where you can specify that it is the unmatched rows that you want to insert.

enter image description here

  1. Add a lookup between your source and destination.
  2. Right click the lookup box to open Lookup Transformation Editor.
  3. Choose [Redirect rows to no match output]. enter image description here
  4. Open columns, map your key columns.
  5. Add an entry with the table key in lookup column , lookup operation as enter image description here
  6. Connect lookup box to destination, choose [Lookup no Match Output]