SSIS Extraction/DTSX解析T-SQL返回ObjectName列为NULL修复求助

问题根源 #

• 缺失SSIS对应XML命名空间声明，XPath无法匹配带前缀的节点
• 硬编码的../相对层级写法兼容性差，不同版本SSIS生成的dtsx文件节点层级存在差异

修复后完整脚本 #

-- 先声明SSIS标准命名空间，URI对应你使用的SSIS版本即可，以下是2012+通用版本
WITH XMLNAMESPACES (
    'http://schemas.microsoft.com/SQLServer/Dts' AS DTS,
    'http://schemas.microsoft.com/sqlserver/Dts/Tasks/SQLTask' AS SQLTask
)
SELECT 
    -- 改用ancestor轴直接找最近的父可执行节点，不用数层级
    Pkg.props.value('ancestor::DTS:Executable[1]/DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ObjectName, 
    Pkg.props.value('(@SQLTask:SqlStatementSource)[1]', 'NVARCHAR(MAX)') AS SqlStatement 
FROM (
    select cast(pkgblob.BulkColumn as XML) pkgXML 
    from openrowset(bulk '\\MYDTS.dtsx',single_blob) as pkgblob
) t 
CROSS APPLY pkgXML.nodes('//DTS:ObjectData//SQLTask:SqlTaskData') Pkg(props) 
UNION 
SELECT 
    Pkg.props.value('ancestor::DTS:Executable[1]/DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ObjectName,
    Pkg.props.value('data(./properties/property[@name=''SqlCommand''])[1]', 'varchar(max)') SqlStatement 
FROM(
    select cast(pkgblob.BulkColumn as XML) pkgXML 
    from openrowset(bulk '\\MYDTS.dtsx',single_blob) as pkgblob
) t 
CROSS APPLY pkgXML.nodes('//DTS:Executable//pipeline//components//component') Pkg(props) 
WHERE Pkg.props.value('data(./properties/property[@name=''SqlCommand''])[1]', 'varchar(max)') <>''

补充排查方案 #

如果修改后还是返回NULL，按以下步骤验证：

1. 单独执行子查询select cast(pkgblob.BulkColumn as XML) pkgXML from openrowset(bulk '\\MYDTS.dtsx',single_blob) as pkgblob，点击输出的XML结果查看结构，搜索ObjectName确认节点位置
2. 如果你使用的是2016及以上版本SSIS生成的包，ObjectName大概率直接作为DTS:Executable节点的属性存在，直接将取值逻辑改为Pkg.props.value('ancestor::DTS:Executable[1]/@DTS:ObjectName', 'varchar(MAX)')即可

原脚本运行结果参考：

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