Gunter Staes's Blog on the Microsoft Platform

Metadata Intelligence Tools in SQL Server 2005

In order to solve the metadata challenge a team at Microsoft has developed a free set of utilities and samples called the SQL Server BI Metadata Samples Toolkit. The kit gives you a jump start in developing your own solution for impact analysis or you can take the Microsoft solution right out of the box. SQL Server BI Metadata Samples Toolkit includes the following items to help you create your own metadata solution:

·         DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.

·         DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.

·         Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.

·         Lineage Repository – A database called SSIS_META that can be used to house metadata from nearly any system.

·         Reports – Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.

·         Report Model – A report model that you can use with Report Builder to allow end-users to create ad-hoc reports.