The SSIS package definitions are stored in msdb.dbo.sysssispackages. Because the definition is XML you can cast it to the XML data type and then operate on it using XQuery. A carefully crafted query could deliver you all the SQL Statements contained in every package. However, many times SSIS developers will use Variables and Expressions to build SQL statements at runtime so I would recommend you only view the information in the table as an initial discovery tool and not the authoritative answer as to whether something will be impacted by a change.

SELECT CAST(CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) AS xml_package_definition,

*

FROM msdb.dbo.sysssispackages

WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE '%%';