I have a SSIS requirement to VERIFY MemberIDs from one Server with MemberIDs on another Server. I need to create a matched list and an unmatched list.
I need to compare data from these 2 tables like this:
select memberID from server1.dbo.table1 t1
select memberID from server2.dbo.table2 t2
select t2.memberID from server2.dbo.table2 t2
where t2.memberID in
(select t1.memberID from server1.dbo.table1 t1)
I believe I'll need to use a VARIABLE to capture the memberIDs from Table1 and use this variable in the compare SQL in order to do the comparison.
Is this the best way or are there better ways to handle this within SSIS?