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.

Category:

Education

Tags:

• BI
• Business Intelligence
• Excel
• Import
• Integration Services
• Lookup
• Lookups
• Microsoft
• Microsoft SQL
• SQL
• SSIS
• Microsoft SQL Server
• Software
• software tutorial

[kumar75794]

Very clear.. Thank you so much..

Have you posted any video for SCD?

[PCTeachME]

@kumar75794 Hi Sorry For The Delay, Thanks for the positive comment!

No just yet but Slowly Changing Dimensions are on my to-do List. Thank you for the request and watch this space.

[jaspreetsinghbal]

keep it up..very useful

[PCTeachME]

@jaspreetsinghbal No problem!  Thanks for the feedback

[amitdotchauhan]

excellent details on lookup ... special cache difference on 2k5 & 2k8

[PCTeachME]

@amitdotchauhan Thanks for your comments!

[wwwujeksam]

Really good video, the only problem is I can't log in on PCTeach.me website to get sample files. But one way or another, thank You very much.

[PCTeachME]

@rBuck50Suck Glad you like! Check out the Fuzzy Lookup video I also posted.

[rBuck50Suck]

Very nice!!