How to create NOT IN on different database servers with SSIS package?

Problem:

There is a source table and destination table with same structure, but in different databases and different servers . Suppose some of data from source table transferred to destination
table in past, and the ID field is not IDENTITY, so all data in
destination table has equivalent in source table. Now we want to
transfer new data from source table to destination table.

Challenges:

1- Suppose source table has very large amount of data, and this
is impossible to transfer all of data and then select new with NOT IN
keywords.

2- source and destination is not in same sql server ,so this is impossible to left outer join .

3-
The ID’s of tables are not linear and there is no datatime field there,
So this is impossible to fetch last inserted rows and fetch source
records from there.

Solution:

1. Create an OLE DB Data Source for source table, select all data from source table, remember you must write order by ID
2. Create an OLE DB Data Source for destination table, select all data from destination table, remember you must write order by ID
3. Go to advanced editor of each Data source and set IsSorted to
   true in output columns, maybe you need to set SortKeyPosition for ID
   field too.
4. Add a Merge Join Transformation
5. use Data Source of source table as Left, and Data source of destination table as right
6. select join type as Left Outer JOIN
7. select all fields of source and ID field from destination, and name the ID from destination as DestinationID
8. Add a Conditional Split Task
9. Create an Output name Case 1 with this condition : ISNULL(DestinationID) 
10. Create an OLE DB Data Destination for destination table
11. Connect Case 1 from Conditional Split Task to Data Destination

The Merge Join apply left outer join on different data sources and
create List of records from source table , all ID’s from source that
has no equivalent in destination will have Null values in left outer
join

Then the Conditional Split Task apply an ISNULL check and select only records that has no equivalent in destination .

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.