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

Answer 2

Firstly I suggest you to load Flat file data into OLEDB staging table, Which is optional. If you Load flat file data into destination, you can align primary keys for the look up.

Create a staging table to insert unmatched records, you will only need asynchronous transformation in this case

Refer Below for Look up Transformation

Check for Look up Columns:

Here is example