Master Data Services – SSIS MDM Load



Master Data Services – SSIS MDM Load


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.



Kirk Haselden has provided a white paper on The What, Why, and How of Master Data Management




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.




An additional reference for understanding MDM is David Loshin’s book.


Ira Warren Whiteside

Actuality Business Intelligence



"karo yaa na karo, koshish jaisa kuch nahi hai"

"Do, or do not. There is no try."



About irawarrenwhiteside

BI Architect
This entry was posted in Computers and Internet. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s