Lookup transformation not seeing new records
By default, the lookup transformation uses full cache and doesn't refresh it's data. You would need to change the lookup transformation to "No Cache" to make sure it executes a query every time the task has to look up a value:



Please refer to the guidance on the SSIS team blog:

Lookup cache modes


  When to use no cache:
  
  
  When your reference table is changing (inserts, updates, deletes) 
  ...