How to get unmatched data between two sources in SSIS Data Flow?

Question

I have two data sources, one sql table, one flat file (csv). Both sources have exact same columns. Example Data:

Table:

HCN  Name  Surname   DOB
111  John   Black    2013-12-10
222  Jack   White    1989-01-14
333  Brian  Brown    2000-04-22

FlatFile:

HCN  Name  Surname   DOB
111  John   Black    2013-12-10
444  Alex   Smith    1978-05-16

Note that the column HCN is the primary key. What I need to do is to get such records included by the table but FlatFile.

Expected output:

HCN  Name  Surname   DOB
222  Jack   White    1989-01-14
333  Brian  Brown    2000-04-22

I have to do it in Data Flow of my SSIS Package. I am doing below to get the matching records (HCN:111), but how I can get the unmatched ones I could not figure out. Any help would be appreciated.

Answer 1

SOLUTION 1 : LOOKUP:
You can follows theses steps:

• add a lookup transformation
  in connection tab, choose your flatfile connexion
  in column tab, drag and drop the Join column in general tab, handle not matching entries by redirect rows
• Redirect the non matching output to your destination

SOLUTION 2 : LEFT ANTI JOIN

You can follows theses steps:

1 Sort datasets / or modifiy the properties of the source to isSorted = true

2 Use a LEFT JOIN on the key, and add a new column containing the id of the right side

3 Add a conditional split condition on right side ID is null

Then redirect CASE 1 splited data to your destination, you have only rows from the left side without right side correspondance