Handling Late Arrivals In SSIS

Peter Schott shows us a pattern for dealing with late-arriving dimension members in SQL Server Integration Services ETL packages:

The general steps are

1. Set up your source query.

2. Pass the data through a Lookup for your Dimension with the missing results routed to a “No Match” output.

3. 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).

4. Do another lookup using a “Partial Cache” to catch these newly-inserted members.

5. Use a UNION ALL transform to bring the existing and late-arriving members together.

Click through for more information and a helpful package diagram.