Microsoft recently released Master Data Services in SQL Server 2008 R2 CTP. Without getting into the strategic benefits, definitions etc. of MDM(Master Data Management) this tool will be very helpful for any application in automating the setup and maintenance of look up or cross reference tables or utilizing reference data.

Nick Barclay: BI-Lingual has provided an excellent post on getting started with this tool. In his post he provide a walkthrough on how to set up a sample Geography Model and the associated Entities, Attributes and Hierarchies. In addition he provides the TSQL to load them. I have expanded on his work and provided a basic SSIS Package for loading the Model.

The approach was to use derived column transforms to set up the required metadata and aggregate transform to generate distinct results. Obviously Nick has done all the heavy lifting I thought it would be helpful to have an SSIS Package to compliment his very informative post.

Last I intentionally kept this package basic, they are many opportunities for improvement and making the entire process data driven, which I will explore next.