It is hard enough to manage your data repository, modify it to meet new business needs, and add to that: keeping documentation and metadata up to date and relevant for your consumers.

Several friends of mine who are Informatica fans let me know on a repeated basis that the data manager in the Informatica Suite helps them out here; while functional; I was never fully impressed with the feature set. I showed my colleagues the SSIS API and they were blown away with how much untapped information sat inside SSIS.

The next question – why hasn’t Microsoft put any of the metadata features directly into the SSIS tool?

While I cannot answer that, I do know that Microsoft has a very active user community and has supported this exact type of development. It is, in fact, nothing new and you can get the SQL Server 2005 Metadata white paper from Microsoft off of the download center, available from December 19, 2005 forward.

The trick though is the samples – for some reason, they seem to have an issue in keeping the sample projects online, so I have made it available from my site as well so insure that it is available to the general public. (See below)

What the MSI contains:

  • 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.
  • Integration Services Samples – A few sample packages to start auditing and viewing lineage on.

For anyone who reads from the Kimball line, the ideas presented in this paper look familiar, particularly if you have read the Microsoft Data Warehouse Toolkit book. In chapter 13 of this book, they discuss a metadata plan for a warehouse — the website has extra materials: sample scripts that make use of the extended properties and reporting services reports to pull out metadata repository information.

I encourage reading both the whitepaper and the book, particularly the Kimball book as the Whitepaper assumes that you know the importance of metadata in your warehouse. The Kimball Microsoft Warehouse book is great in that it takes the concepts that they have developed over the years and applies it directly to the Microsoft stack.

SQL 2005 Metadata Samples (.msi, 311 kb)

Feel free to contact me for any questions. Thanks!