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.You can then use an Execute SQL task to run your update in a more organic manner.Set up your data flow as shown above to pull in the data to a staging table, then you can run a single UPDATE statement: The advantage here is that you’re executing the expensive UPDATE statement once for each table rather than once for each row affected.Then we use a conditional split to find out the change type (removed, new, or existing records).
2- Create another OLE DB Source for destination table.
Today I want to extend this to cover DELETED records as well.
So method used in this post can be used to find INSERTED / UPDATED / DELETED records from the source table and apply those changes into the destination table.
You use this transformation because you have to use a parameterized UPDATE statement to update one row at a time with the new column values.
You can also use destination components to apply updates.