Filtering a SQL Table With Another Table

Question

Good afternoon, all -

I have a table of newly imported data and I need to filter out of that table matching rows found in another table, leaving me with just the new rows. One snag might be that the data on table 1 is on a different server than the data on table 2, but I figure just adding a connection manager for each server would be sufficient to cover that issue.

I seem to recall seeing a task that could do something like this, but can't find it to save me. Does anyone have any ideas?

Thanx in advance for any assists!

Answer 1

Hi @Adam Quark ,

We can use OLEDB source and Lookup Transformation to get match data and no match data.
We should set Specify how to handle rows with no matching entries as Redirect rows to no match output in Lookup general page.

Please refer to the following pictures:

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@Adam Quark

You can create a SSIS package with Data Flow Task for this purpose
Inside Data Flow Task, have two OLEDB sources to connect to your two server tables ordered by your join columns. Then use a Merge join and connect both OLEDB sources to both inputs. Make sure you set isSorted property to true for both outputs coming from OLEDB sources and mark sorted columns SortKey value as 1,2 etc in output columns
Inside Merge Join choose join type as Left join. Then take Merge Join output and link it to Conditional Split task. Add an Output inside for condition

ISNULL([Column]) == TRUE  

Where [Column] is the join column from your second table. Then link the conditional split output to your desired destination to get only the unmatched rows