These errors can be dealt easily at ETL Level, sometimes we got orphan records in Fact table which means we got a Key in Fact for which no entry in Dimension or Master Table.
We can use Lookup Task in SSIS to deal with such scenarios.
1) In Data Flow add OLE DB Source pointing to source table, in our case its SCD.DBO.SALES
2) Add Lookup & Connect Source to Lookup
In General Tab Select Cache Mode and Connection Type
3) Connection Columns Select the Table in our case its DestinationEmployees
4) Then Select Column Mapping (Join)
5) Add two Destinations, one is pointing to Fact Table and another is pointing to Orphan Table
Just for Testing, I have added 2 File Destination.
After completion, Package will look something like this.