Than you for the test data. There were some errors in it but I got it to work. Here it is for other onlookers:

INSERT INTO dbo.StudentHistory

(StudentId, DepartmentId, ProcessedMonth, ProcessedYear, ProcessedDate, InsertedDate)

VALUES (1, 100, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),

(2, 100, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),

(2, 101, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),

(2, 102, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),

(2, 103, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),

(3, 103, 2, 2013, GETDATE(), '2013-02-01 12:00:00 '),

(3, 104, 2, 2013, GETDATE(), '2013-02-01 12:00:00 ');

INSERT INTO dbo.Student

(StudentId, DepartmentId, InsertedDate, LastUpdated)

VALUES (1, 100, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),

(2, 100, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),

(2, 101, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),

(2, 102, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),

(2, 103, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 '),

(3, 103, '2013-02-01 12:00:00 ', '2013-02-01 12:00:00 ');

I am wondering if you need to bother with any of the Join Transforms in SSIS. Are StudentHistory and Student tables on the same Database Instance, i.e. can they be joined together in a single SQL query like this to add missing rows to the Student table?

INSERT INTO dbo.Student

(

StudentId,

DepartmentId,

InsertedDate,

LastUpdated

)

SELECT DISTINCT

sh.StudentId,

sh.DepartmentId,

GETDATE(),

GETDATE()

FROM dbo.StudentHistory sh

LEFT JOIN dbo.Student s ON sh.StudentId = s.StudentId

AND sh.DepartmentId = s.DepartmentId

WHERE s.StudentId IS NULL;