Alvin Ramard (6/22/2015)

---

winston Smith (6/22/2015)

---

Scenario:

I have a person entity and Nationality Entity. The nationality may or may not be populated (can be null). If null, i want to assign the key for UNKNOWN nationality, a row i placed in the nationality table.

in my populateFactTable package i have a lookup to nationality. If it finds a match, the row moves on through the flow and gets inserted, if not, i was going to push the "no match output" to a derived column shape that contains the key for the UNKNOWN nationality, and then merge these two branches.

This does not sound optimal to me, paritcularly if you have many dimensions that can be unknown.

Generally,whats the best way to deal with this scenario?

I do this in a stored procedure, not in SSIS, but the idea is the same.

You need a variable, let's call it @Default_NationalityKey, that has the value for your "unknown" key. If the lookup fails, then assign this value. In a stored procedure, it would look like:

,[Nationality_Key] = ISNULL(Dim_Nationality.Nationality_Key, @Default_NationalityKey)

In SSIS, the derived column sounds like a good option.

Sounds like you're on the right track.