Peter Schott shows us a pattern for dealing with late-arriving dimension members in SQL Server Integration Services ETL packages:
The general steps are
Set up your source query.
Pass the data through a Lookup for your Dimension with the missing results routed to a “No Match” output.
Insert those “No Match” rows into your Dimension using a SQL task – checking to make sure that this particular row hasn’t already been inserted (this is important).
Do another lookup using a “Partial Cache” to catch these newly-inserted members.
Use a UNION ALL transform to bring the existing and late-arriving members together.
Click through for more information and a helpful package diagram.