Dynamic dependency view SQL and SSIS packages

Question

Hi All,

In my enterprise project we have nearly 800 tables with 7 staging areas. We have developed nearly 100 packages to implement the DW.

Business users constantly chaging the requirements after completing all the packages like table/column names, PK/FK columns with many other changes due to business requirements.

Being changes in the table structure we need to refresh all the packages related to the corresponding talbes changes.

Now, sometimes we are missing the changes to packages due to lack of which package will effect of changes to which table.

So I need to know is there any process to track on pakcage dependency so we can easily know on changing any table structure we can directly know which pakcage will effect and needs to setup new metadata.

thanks

prav

Answer 1

Each dtsx file is xml, so you could attempt to parse properties of whatever tasks you are using. But when they change the format, your code will break.

.Net code can load packages into an Application object and you can get at the package that way: http://msdn.microsoft.com/en-us/library/ms211665%28v=SQL.90%29.aspx.

You could also create a documentation database/application and make that part of your deployment and change process.

If you kept all your sql code in stored procedures you could query dependencies in sql.