问题描述
我建立了一个自定义的作业日志框架，该框架记录了在SSIS程序包中执行的所有任务以及所有出现的错误.完成后，需要进行增强以存储SSIS包在SQL Server上执行的所有SQL查询.这不仅限于执行SQL任务，它们还在寻找该包运行的任何SQL查询.我知道OnInformation日志记录是SSIS日志记录框架的一部分，但这仅显示了一些查询.
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.
提前谢谢！
推荐答案
这些是Codeplex上的免费软件，我相信它可以满足您的请求: https://sqlmetadata.codeplex.com/
These is a free software on codeplex which I believe might satisfy your request at: https://sqlmetadata.codeplex.com/
如果需要对其进行编码，则应考虑已部署的SSIS软件包有两种主要类型:传统模式和目录模式.
If you need to code it, you should consider that there are two main types for deployed SSIS packages: Legacy mode and Catalog mode.
旧版模式将pacakges部署到msdb，您可以在其中使用SELECT name, packagedata FROM msdb.dbo.sysssispackages
Legacy Mode deploys pacakges to msdb where you can find using SELECT name, packagedata FROM msdb.dbo.sysssispackages
目录模式使用SSISDB，您可以使用catalog.get_project返回project_stream，它表示包含项目中软件包的zip文件.您可以参考如何导出软件包部署在集成服务目录"中；使用任何命令行(C#还是T-SQL?.
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?.
拥有软件包的XML之后，您可以轻松地确定要导出的组件.
After having the packages' XML, you can easily identify which components you want to export.
这篇关于获取从SQL Server上的SSIS包执行的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！