Transform to remove rows from data set A that match rows in data set B on a given key?

Hi,

I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.

E.g. If I have the following data:

Main input:
Key Name
--- ----
1 Steve
2 Jamie
3 Donald

Second Input
Key DontCareAboutThisField1
--- -----------------------
1 ...
3 ...
4 ...

Then I would like the following output:

Main Output
Key Name
--- ----
2 Jamie

Second Output
Key Name
--- ----
1 Steve
3 Donald

Can I do this with a standard transform, or will I have to write my own Any help on this would be greatly appreciated!

Thanks in advance,

Lawrie

Answer this question

Transform to remove rows from data set A that match rows in data set B on a given key?

Chrisull

A LOOKUP accomplishes this very well. Read this article: http://www.sqlis.com/default.aspx 311

-Jamie
Transform to remove rows from data set A that match rows in data set B on a given key?

Answer this question