I was chatting with some other SQL Community members recently and the topic of handling late-arriving members for a warehouse load came up. I figured it was worth taking a couple of minutes to share the general concept I’ve been using to handle late-arriving members in SSIS. The steps are relatively straightforward if you’ve done much SSIS, but sometimes the little things can trip you up. The example below is based loosely on the WideWorldImporters database samples from Microsoft.
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.
Sample code for the Inferred Insert:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters