SSIS Update (Replace) record instead of append based on Primary Key
As Kin mentioned in his comment above, it is possible to accomplish what I was trying to do using the SSIS Lookup transformation component (example also provided in his comment) which answers my question.

While I will surely be using this component in the future I decided to go with a more straightforward SQL scripting method to accomplish what I needed after reading mskinner's comment (and referenced blog) which points out that using an Execute SQL task is probably going to be much faster/efficient than using the Lookup Transformation component in SSIS for what I am trying to accomplish.