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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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 .

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply