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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.