如何查看SSIS中Execute SQL任务逻辑及包内查询信息？

一、获取SSIS包与关联作业的目录信息 #

要生成包含关联作业的SSIS包目录，可通过关联SSISDB目录表和msdb的作业表实现：

SELECT 
    p.name AS package_name,
    p.package_id,
    s.folder_name,
    sj.name AS job_name,
    sj.job_id,
    sjs.step_name,
    sjs.command AS job_step_command
FROM 
    catalog.packages p
JOIN 
    catalog.folders s ON p.folder_id = s.folder_id
LEFT JOIN 
    msdb.dbo.sysjobsteps sjs 
    ON sjs.command LIKE '%' + p.name + '%' -- 匹配作业步骤中调用的包名
LEFT JOIN 
    msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
ORDER BY 
    s.folder_name, p.name;

二、提取SSIS包内的查询语句与目标表 #

internal.event_messages仅存储运行时日志，无法直接获取包设计阶段的查询或目标表，需从catalog.packages的XML格式包定义中解析：

1. 提取Execute SQL任务中的查询语句 #

WITH XMLNAMESPACES (DEFAULT 'www.microsoft.com/SqlServer/Dts', 'www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT 
    p.name AS package_name,
    s.folder_name,
    x.value('@Name', 'varchar(255)') AS task_name,
    x.value('(./SQLTaskData/SQLStatement)[1]', 'nvarchar(max)') AS sql_query
FROM 
    catalog.packages p
JOIN 
    catalog.folders s ON p.folder_id = s.folder_id
CROSS APPLY 
    p.package_data.nodes('/Executable/Executables/Executable[contains(@ExecutableType, "SQLTask")]') AS t(x)
WHERE 
    x.exist('./SQLTaskData/SQLStatement') = 1
ORDER BY 
    s.folder_name, p.name, task_name;

2. 提取数据加载的目标表 #

针对Data Flow任务中的OLE DB目标等组件：

WITH XMLNAMESPACES (DEFAULT 'www.microsoft.com/SqlServer/Dts', 'www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT 
    p.name AS package_name,
    s.folder_name,
    df.value('@Name', 'varchar(255)') AS data_flow_task_name,
    dest.value('@Name', 'varchar(255)') AS destination_component_name,
    dest.value('(./Properties/Property[@Name="OpenRowset"])[1]', 'varchar(255)') AS target_table
FROM 
    catalog.packages p
JOIN 
    catalog.folders s ON p.folder_id = s.folder_id
CROSS APPLY 
    p.package_data.nodes('/Executable/Executables/Executable[contains(@ExecutableType, "Pipeline")]') AS t(df)
CROSS APPLY 
    df.nodes('./ObjectData/Pipeline/components/component[contains(@ComponentType, "Destination")]') AS d(dest)
WHERE 
    dest.exist('./Properties/Property[@Name="OpenRowset"]') = 1
ORDER BY 
    s.folder_name, p.name, data_flow_task_name;

三、扩展原查询获取运行时SQL/表信息 #

如果要从internal.event_messages提取运行时执行的SQL或目标表信息，可筛选特定事件类型：

SELECT 
    event_message_id,
    execution_path,
    package_name,
    package_path_full,
    event_name,
    message_source_name,
    package_path,
    message AS runtime_sql_or_table_info -- 部分事件会包含执行的SQL或目标表详情
FROM 
    internal.event_messages (NOLOCK)
WHERE 
    event_name IN ('SQLStatementExecuted', 'OnInformation')
    AND (message LIKE '%SELECT%' OR message LIKE '%INSERT%' OR message LIKE '%UPDATE%' OR message LIKE '%DELETE%' OR message LIKE '%FROM%' OR message LIKE '%INTO%')
ORDER BY 
    event_message_id DESC;

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