7

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.

enter image description here

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • Use a Lookup instead of a Merge Join, then use the Not Matched output. https://stackoverflow.com/questions/31253755/not-exists-in-ssis-data-flow – EzLo Jun 04 '18 at 13:25
  • 1
    Possible duplicate of ["NOT EXISTS" in SSIS data flow](https://stackoverflow.com/questions/31253755/not-exists-in-ssis-data-flow) – EzLo Jun 04 '18 at 13:28
  • Receiving error: "The destination component does not have any available inputs for use in creating a path" when I try to connect both source files to a lookup ?? @EzLo – Eray Balkanli Jun 04 '18 at 13:31
  • you don't have to connect the reference table to the lookup, you set it up in the lookup connections directly (it only has 1 input as flow). – EzLo Jun 04 '18 at 13:35
  • 1
    I don't think you can do a Lookup from a Flat File Connection, can you? – Tab Alleman Jun 04 '18 at 13:50
  • 1
    Load the contents to a cache manager in a previous data flow, then you can use the lookup against the cache. – EzLo Jun 04 '18 at 13:54

2 Answers2

9

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

enter image description here

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

enter image description here

Kobi
  • 2,494
  • 15
  • 30
  • 1
    I justs add an alternative solution LOOKUP as suggested by comments – Kobi Jun 04 '18 at 13:59
  • 1
    You cannot use a Flat File in a Lookup. Are you suggesting to put the flat file into a "cache" connection, and THEN using it in a Lookup? – Don Cheadle Dec 19 '18 at 05:01
3

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 enter image description here

Check for Look up Columns:

Here is example

enter image description here

Ven
  • 2,011
  • 1
  • 13
  • 27