Using SSIS and SSMS - Attempting to create an ETL design document that details source and destination columns and their relations

Question

I am attempting to create an ETL design document that details source and destination columns and their relations, by looking at metadata for the columns. The format should be similar to below:

Final document format

I have utilised something similar to the site below to get data for the output/destination database:

MS SQL TIPS - list-columns-and-attributes-for-every-table-in-a-sql-server-database

Now I am trying to get the same information for the source database but I am not sure if I am allowed to run such a query directly on the source as it is important data.

Is there a way I can use SSIS or look at the source in SSMS to see all relationships I need?

I have the packages in SSIS that details as to what transformations I will apply to the source, via sql queries. I've tried looking at packages individually but there are a lot and there should be an easier way I am missing.

Answer 1

Depends on your sources. If they are all queries from tables, you can probably parse them from the .dtsx files. If any of them are stored procs or views, then there's probably nothing you can do without querying the source database.