SSIS – Lookups

When either importing data or ETL (Extract, Transform & Load) for a data warehouse, you quite often need to assign key values to your data so you can use these values to join/link on. For example, if you have a list of a spreadsheet which contains region information you may need to assign the region ID to the destination table. When the ID is present you can use this new key to join your data together. In the ETL world these quite often are referred to as a Surrogate key as you are replacing the original text/ID with a new key of your choosing.

This video will explore how to perform a look up to 3 levels; Basic – a standard lookup, Intermediate – a Lookup with simple error handling & Advanced – a lookup with transformation of the errors.

As this video discusses a topic that warrants an hour of time to explain, I have opted to create a series of example files (for SQL 2008) which can be obtained on my website (briefly discussed at teh beginning of the video). If you opt to download these files it will allow you to play around with all the permutations.