SQL Server SSIS packages metadata solution pack
Do you have hundreds of SSIS packages to manage?
Do they run at various times to address the ETL solutions within your environment?
Let's say you have to modify the base table columns, so you need to consider what kind of impact this change would have affect on the various SSIS packages that are dependant within this base table. Have you ever seen the MetaData solution pack, that is one of the discovery tools you can use to explore the impact of a change like this.
Even I wasn't aware of this package until last month when I need to assess such a changes impact, and the approach that was taken in the SQL Server BI Metadata Samples Toolkit is phenominal as the solution can load up the package into memory as the runtime engine does and walk the package using the SSIS API. This solution pack was developed as a free set of utilities and samples called the SQL Server BI Metadata Samples Toolkit.
The impact analysis that provides will have the following files (that was referred in Technet article) that can be adopted based on your problem:
· 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.
The last one IS samples are very good to look at the audit solutions for your SSIS packages, with the help of report model can give further information on the assesment. For the sake of usage these files you have to create an empty database then you can populate by running the SSIS_META_creation.sql script that is provided along with this pack. Dependency Analyzer file can be used to scan the local default instance pakages by using as a scheduled job along with SQLAGent. To know more about the parameters for this pack you can run DependencyAnalyzer.exe /? to see all the additional switches you can pass to the program.
Further the DependencyViewer.exe executable ships with SQL Server 2005 BI Metadata Samples Toolkit to help you navigate through the dependencies. The tool helps you run a quick impact analysis graphically to determine what packages will be impacted by you making a change to a data source. In the DependencyViewer’s interface, click the Load button to connect to the local repository and browse through the tree. If you select a SQL Server table, you will see (blue colored) where in the package the table is used. The below example shows you some sample Anaylsis Services cube that ship with SQL Server and how the impact analysis would look on one of those data sources. If you select an object, the box turns blue and you can see the description of the object (if one exists) in the Object Properties window.
To download refer this "SQL Server 2005 Business Intelligence Metadata Samples Toolkit and to know more about this refer to this associated whitepaper, that will enable to test and make it possible to satisfy many of your metadata needs - simply and essentially for free!