FunkyDexter (2/8/2016)
If you want to keep this matching process wholly within SSIS
It's a strong driver but not an absolute imperative. I want to keep as much in SSIS as possible but a few bits of sql won't hurt if there's a bottleneck.
Knowing that my approach is broadly right from a SSIS point of view is good to hear, though. I'm no expert at SSIS so it's hard to know what I don't know.
I mentioned performance purely because the merge join component in SSIS is semi-blocking and join performance in the DB engine usually blows it away.
If you do not have huge volumes of data to match and the process appears to work OK, go with it.
Some quick background reading here.