如何在SSIS中自定义日志消息并跟踪包内执行的SQL查询？

一、自定义日志记录实现（针对Execute SQL任务） #

• 任务后追加日志步骤：在每个Execute SQL任务执行完成后，新增一个专门的Execute SQL任务，将执行详情写入自定义SQL Server日志表。日志表可设计包含字段：包名称、任务名称、执行时间、执行SQL语句、目标表名、执行状态。
  示例日志写入SQL：

  INSERT INTO SSIS_Execution_Log (PackageName, TaskName, ExecutionTime, ExecutedSQL, TargetTable, Status)
  VALUES (?, ?, GETDATE(), ?, ?, 'Success')
  

  其中参数可通过SSIS变量传递，提前将包名、任务名、执行SQL、目标表存入变量，再映射到日志任务的参数中。
• 利用事件处理程序批量处理：给包或Execute SQL任务添加OnPostExecute事件处理程序，在事件流中加入Execute SQL任务。通过系统变量@[System::TaskName]、@[System::PackageName]获取上下文信息，若为Execute SQL任务，需提前将SQL语句存入变量，再读取变量内容写入日志表，无需逐个任务手动配置。

二、构建SSIS包信息目录的有效方案 #

• 扩展内置日志体系：SSIS默认支持将日志写入SQL Server，启用后可获取包执行基础信息。在此基础上结合自定义日志，将SQL语句、目标表等信息关联到内置日志表中。旧版依赖sysssislog表，SQL Server 2012及以后可使用SSISDB目录中的视图。
• 批量解析包XML：SSIS包本质为XML文件，可编写SQL脚本或PowerShell脚本，批量解析存储在SSISDB或文件系统中的包文件。通过OPENXML函数定位DTS:Executable节点，提取所有Execute SQL任务的SQL语句、目标连接等信息，存入自定义目录表。
• 用扩展事件捕获全量SQL：若需捕获数据流任务中隐式执行的查询，可使用Extended Events跟踪sql_statement_completed事件，筛选SSIS执行进程发起的语句，关联对应包和任务，实现全量执行SQL的捕获。

三、自动化报表实现 #

基于上述日志表和目录表，编写SQL查询生成包含包名称、执行时间、执行SQL、目标表、执行状态的报表内容。将查询保存为视图后，通过SSRS或Excel Power Query连接数据源，配置定时刷新即可实现自动化报表。

内容的提问来源于stack exchange，提问作者tomfbsc