Improving performance of mapping standardized values with non equi joins - LIKE operator
Thanks to everyone's comments and suggestions, I tried all of the ideas @mustaccio posted, but in the end the fastest strategy was to abandon the mapping table altogether, and just go with a big ugly CASE statement to handle all of the situations, and insert that into another staging table. Execution time dropped to only a few seconds, and I can't argue with that. The situations to handle are mostly static, so if I have to re-deploy a SSIS package once or twice a year to add new situations if they come up, so be it.

Final example insert statement (not exact, just going off memory):

INSERT INTO STAGINGTABLE_TWO (TextColumn) 
SELECT 
  CASE
    WHEN TextColumn LIKE '%FOO BAR%' THEN 'Foo Bar'
    WHEN TextColumn LIKE '%FOO%' THEN 'Foo'
    WHEN TextColumn LIKE '%BAR%' THEN 'Bar'
    ELSE 'Unknown'
  END
FROM STAGINGTABLE;