Tracking SQL Dependencies within Stored procs / SSIS / SSRS

Question

Hi All,

We have an environment where we develop within T-SQL, SSIS and SSRS. We have core databases for our own systems, but we have dependencies to another SQL system that we directly extract data from by calling tables, views and stored procs (not ideal, but that is for another day).

As a developer I want to make sure that at any point in time I can identify all of my dependencies on the secondary database. This will hopefully allow me to identify areas of concerns during upgrades and allow for better communication with the secondary team to ensure that any potential changes required to our systems are identified early.

Is anyone aware of any tools / methods that will intelligently identify all DB objects used within:

• SSIS Packages
• SSRS Reports
• Calls to objects through a linked server in stored procs, etc

My current alternative is to manually track all the objects we use - but I would MUCH prefer some way of automating this to eliminate human error in the process. We are currently moving towards a TFS environment for our source control, so any plug-ins for this would be a big bonus!

Thanks

Answer 1

There is system metadata available within the data dictionary that will track dependencies on database artifacts right down to the column level. You can see what tables and columns a particular stored procedure depends on.

However, for objects external to the database such as reports or SSIS packages the database does not have this information to track, so there is nothing within the data dictionary that can help you there.

What you want is provenance reporting, and that is something of a holy grail in ETL tool circles. The theoretical nirvana is having a tool that allows you to set up ETL processing or reporting (preferably without having to write any SQL code) and then to produce a push button report that tracks the outputs back to their origin.

Sadly, neither SSRS or SSIS have a facility to do this. You can programatically traverse SSIS data flow metadata so could do this in theory (one can algorithmically collapse a graph of such dependencies down to a minimal form), but doing this to SQL is quite a bit harder. I'm not aware of any off the shelf tooling that does a decent job of this.

Answer 2

Hi Chris, if you happen to find something like this, I am looking for the same thing. Please post if you find something.

Thanks Rich

Answer 3

Chris, I actually took another look around and I found a nice key in to the SSIS, SSRS, and SSAS world. The SQL MetaData ToolKit (http://sqlmetadata.codeplex.com/releases/view/47356) will slice and dice the objects for you and throw them in to a table. If you've been looking, you probably saw it and thought it looks goofy. It does but when I installed the latest version I remembered the database being behind it so I hacked in to it. Pretty easy to follow. Then the stuff in the database you can get from system tables. sysdependencies has its issues where it gets out of synch so what I did was script the databases and recreated them on a fresh dummy server. I hope this helps. I'll be working on the same type of thing.

Rich

Answer 4

http://www.red-gate.com/products/sql_dependency_tracker/index.htm

Not quite sure how you can link SSRS/SSIS to this.

Can't believe red gate aren't doing it, think they're missing a trick.