INSERT INTO StudentHistory(1,100,2,2013,GETDATE(),'2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(2,100,2,2013,GETDATE(),'2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(2,101,2,2013,GETDATE(),'2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(2,102,2,2013,GETDATE(),'2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(2,103,2,2013,GETDATE(),'2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(3,103,2,2013,GETDATE(),'2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(3,104,2,2013,GETDATE(),'2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(1,100,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(2,100,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(2,101,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(2,102,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(2,103,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')
INSERT INTO StudentHistory(3,103,'2013-02-01 12:00:00 ','2013-02-01 12:00:00 ')
Try to insert this first later build ssis to perform left outer join with StudentHistory as Left source and Student as right Source on conditions studentid and departmentid. I have used Merge join
Now INSERT INTO StudentHistory(2,104,2,2013,GETDATE(),GETDATE())
run the ssis my issue is i am doing lookup for a studentid 2 to a department while doing so i should get the below data
Source Destination
StudentIdDepartmentId StudentIdDepartmentId
2 100 2100
2 101 2101
2 102 2102
2 103 2103
2 104 NULLNULL
but i am receiveing
Source Destination
StudentIdDepartmentId StudentIdDepartmentId
2 100 NULLNULL
2 101 NULLNULL
2 102 NULLNULL
2 103 NULLNULL
2 104 NULLNULL
Could u please guide me on this