Search code examples
sqlsql-serverssisetlsql-server-data-tools

How Intersect and Except result in SSIS

Have Two result Set A and B, In SSIS Merge Join how get EXCEPT and INTERSECT of A result set.

enter image description here

Bellow sql syntax return desired out put: 

---Insersect
select a.* from A a Inner join B b on a.Key=b.Key 

--Except
select a.* from A a left join B b on a.Key=b.Key where b.Key is null

How to get those query result from SSIS merge join

Solution

  • According to his Microsoft Article, "The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join"

    In the Merge Join Transformation editor, you can select the Join Type property to do that:

    Except:

    select a.* from A a left join B b on a.Key=b.Key where b.Key is null

    You have to set Join Type = Left Outer Join, and after the Merge Join Transformation you have to add a conditional split to filter rows that has b.Key is null

    Follow this detailed article for that: Get all from Table A that isn't in Table B

    Intersect

    select a.* from A a Inner join B b on a.Key=b.Key

    You have to set Join Type = Inner Join

    Follow this article for that: Inner join in SSIS