I’ll confess, our shop is not the most organized in the world. We are still working on certain items that I would hope most shops have long ago put in place as part of their standard development cycle. Items such as aggregating a list of all of the tables that each package touches (input vs. output vs control) so that impact analysis can be preformed when looking at changing a table structure.
Well, if such a document (or database / table / whatever) is not available, it is good to remember that SSIS packages are really little more than text based XML files. As such, you can easily search through the repository, or list of folders, containing your packages for the name of the table which you are trying to analyze.
I have found that using the dos based findstr command works wonders when compiling a list of packages to be worked.
C:\FrameworkFolder\> findstr /s /i /m "myTableName" *.*
You can also put in wild cards per normal dos rules. For instance a series of table name that have YYYYMM after them might be found by the following:
C:\FrameworkFolder\> findstr /s /i /m "myTableName??????" *.*
Or any tablename that starts with that might be found by:
C:\FrameworkFolder\> findstr /s /i /m "myTableName*" *.*