I'm currently working on a documentation project related to SQL Server Integration Services (SSIS) packages that are executed through SQL Server Agent jobs. In other words, I want to create a mapping that indicates which tables are accessed and manipulated by each SSIS package.
I have created scripts to retrieve the list of SSIS packages executed by these jobs, and I also have a comprehensive list of all the tables in each database within the SQL Server instance.
Here's the specific information I have:
List of SSIS Packages: I have successfully retrieved a list of all SSIS packages that are executed through SQL Server Agent jobs. This list includes package names and descriptions where available.
List of All Tables: I have generated a comprehensive list of all tables present in each database within the SQL Server instance. This list includes table names and column details.
Now that I have both the list of SSIS packages and the list of tables, I need to associate each SSIS package with the tables it utilizes. Despite having these two sets of information, I encountered challenges when attempting to combine the results of these queries to get the required mapping between SSIS packages and the list of tables. I'm seeking guidance on how to efficiently associate tables with SSIS packages, preferably using SQL queries or scripts. Thank you for your help!