Lookup Error (Spelling mistake causing 90 record losses)

I'm performing a lookup for a primary key, but one of the values is spelt differently and causing 90 records to error. The simple solution is obviously for me to replace the spelling within the source document. However, this is an assignment so I'm curious to know if there is another (professional) fix within the SSIS project.

Any suggestions?

Regards,
Liamptk

Are you loading a dimension or a fact?

A common technique for loading facts is to first validate the dimension natural keys and infer dimension rows that do not exist. That is, create the row, load the natural key info and hope dimensional information arrives at a later time. This allows you to assign a foreign key to the fact and not have to reject rows.

As far as dimensional natural keys go, they should represent the source system's primary key which should correspond to how the business identifies the entity. Since this should be the key the operational system is using, spelling mistakes should not happen.

The reason the spelling mistake exists is due US English and UK English. Tyre and tire.

There are several other matches I need to make during the lookup to the dimension table. Therefore creating a column isn't the answer. Unless its acceptable to do two lookups?

Well, it's a PK or it isn't. If the database has tyre, then all of the children records will have tyre as well. If you want to cleanse and conform the data you do that before loading to get consistent data. Although, I can't ever recall cleansing a primary key column.

On the source data that field is not acting as a primary key. Can you clean the data within SSIS? Or is this a process that happens prior?