Impact analysis of change in metadata or OLTP Schema on SSIS packages

In a large enterprise environment, you may have dozens if not hundreds of SSIS packages that run at various times to load a myriad of tables. Let’s say that you have a new DBA who decides it will be more optimal to turn a column into a small integer from a varchar field. So, what will be the impact?

The change, no matter how small it is, may create the need to change 15 packages. The metadata solution pack is one of the discovery tools you can use to explore the impact of a change like this.There are a few approaches you could take to analyze the metadata of BI systems like SSIS. One approach you could use is to look at the XML files that make up each package using XPATH queries.

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

Reference: BI Metadata Whitepaper