Get SQL queries which are executed from SSIS package on SQL Server

Question

I have built a custom job log framework which logs all of the tasks which are execute in an SSIS package along with any error that surface. Once complete, an enhancement was requested to also store any SQL queries that the SSIS package executes on the SQL Server. This is not limited to only Execute SQL tasks, they are looking for ANY SQL queries that the pack runs. I am aware of the OnInformation logging that is part of the SSIS logging framework, but this only shows some of the query.

Thanks in advance!

Answer 1

These is a free software on codeplex which I believe might satisfy your request at: https://sqlmetadata.codeplex.com/

If you need to code it, you should consider that there are two main types for deployed SSIS packages: Legacy mode and Catalog mode.

Legacy Mode deploys pacakges to msdb where you can find using SELECT name, packagedata FROM msdb.dbo.sysssispackages

Catalog Mode uses SSISDB, you can use catalog.get_project to return project_stream which represents a zip file containing the packages in the project. You can refer to How to export packages deployed in "Integration Services Catalog" using any command line, C# or T-SQL?.

After having the packages' XML, you can easily identify which components you want to export.