SSIS replace using lookup

SSIS and Import/Export
1

i Have problem migrating a CRM Emails
when i migrate a colummn say CC it may contains IDs from three tables Contact,lead,account,systemuser. FRom these tables IDs from sytemuser are diffrent for both source and destination.

for eg the valuse in CC like

1570123C-8DB6-E611-80E4-005056950FA5;20E6D2CF-8DB6-E611-80E4-005056950FA5;CF8A728F-43B7-E611-80E4-005056950FA5 in this 20E6D2CF-8DB6-E611-80E4-005056950FA5 from systemuser 'ABC' all the others from contact. the Equialant ID for user ABC in '4811DFC6-F351-E711-80EF-005056950FA5'.

please advise me how can I replace CC column if there is any sytemuser there with their corresponding values in destination ?

2

I confess the rules for substitution are not crystal clear to me. I can offer this:

  1. if the rules are simple and don't depend on searching strings, use a derived column transformation. eg. in your sample row, you have 3 GUIDs, or so it seems and you are replacing the second one. This is simple with a DC compoment.
  2. If the rules get complicated, I'd write a script component to do the replacement.