-- http://sqlblog.com/blogs/peter_debetta/archive/2006/07/13/Using-XML-Data-Type-Methods-to-query-SSIS-Packages.aspx
-- Variables and temp tables
DECLARE @x XML
CREATE TABLE #t (PackageName VARCHAR(100),
PackageCode XML)
-- Bulk load the Package XML into @x
SET @X = (SELECT *
FROM OPENROWSET(BULK N'C:\Path To Your Package\PackageName.dtsx', SINGLE_BLOB) AS DOCUMENT)
-- Load the @x into the temp table
INSERT INTO #t
VALUES ('PackageName.dtsx', @x)
-- Query Package Configurations
;
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS p1,
'www.microsoft.com/SqlServer/Dts' AS DTS) -- CTE to declare XML namespaces
SELECT PackageName,
V.Vars.value('./p1:Property [@p1:Name="ConfigurationType"][1]',
'varchar(100)') AS ConfigurationType,
CASE CAST(V.Vars.value('./p1:Property[@p1:Name="ConfigurationType"][1]',
'varchar(100)') AS INT)
WHEN 0 THEN 'Parent Package'
WHEN 1 THEN 'XML File'
WHEN 2 THEN 'Environmental Variable'
WHEN 3 THEN 'Registry Entry'
WHEN 4 THEN 'Parent Package via Environmental Variable'
WHEN 5 THEN 'XML File via Environmental Variable'
WHEN 6 THEN 'Registry Entry via Environmental Variable'
WHEN 7 THEN 'SQL Server'
END AS ConfigurationTypeDesc,
V.Vars.value('./p1:Property[@p1:Name="ConfigurationVariable"][1]',
'varchar(100)') AS ConfigurationVariable,
V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]',
'varchar(100)') AS ConfigurationName,
V.Vars.value('./p1:Property[@p1:Name="ConfigurationString"][1]',
'varchar(100)') AS ConfigurationString
FROM #t CROSS APPLY #t.PackageCode.nodes('/DTS:Executable/DTS:Configuration') AS V(Vars)
-- Query Package Variables
;
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS p1,
'www.microsoft.com/SqlServer/Dts' AS DTS) -- CTE to declare XML namespaces
SELECT PackageName,
V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]',
'varchar(100)') AS VariableName
FROM #t CROSS APPLY
#t.PackageCode.nodes('/DTS:Executable/DTS:Variable') AS V(Vars)
-- Query Package Properties
;
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS p1,
'www.microsoft.com/SqlServer/Dts' AS DTS) -- CTE to declare XML namespaces
SELECT PackageName,
V.Vars.value('attribute::DTS:Name', 'varchar(100)') AS PropertyName,
V.Vars.value('.', 'varchar(100)') AS PropertyValue
FROM #t CROSS APPLY
#t.PackageCode.nodes('/DTS:Executable/DTS:Property') AS V(Vars)
-- Query Package Executable Hierarchy
;
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS p1,
'www.microsoft.com/SqlServer/Dts' AS DTS) -- CTE to declare XML namespaces
, Executables AS
(SELECT PackageName,
CAST('' AS VARCHAR(100)) AS ParentNodeName,
V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]',
'varchar(100)') AS NodeName,
V.Vars.query('.') AS CurrentNode,
V.Vars.query('./p1:Executable') AS ChildNodes,
V.Vars.value('./p1:Property[@p1:Name="ConfigurationType"][1]',
'varchar(100)') AS ConfigurationType,
1 AS LEVEL
FROM #t CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS V(Vars)
UNION ALL
SELECT PackageName,
Executables.NodeName AS ParentNodeName,
V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]',
'varchar(100)') AS NodeName,
V.Vars.query('.') AS CurrentNode,
V.Vars.query('./p1:Executable') AS ChildNodes,
V.Vars.value('./p1:Property[@p1:Name="ConfigurationType"][1]',
'varchar(100)') AS ConfigurationType,
Executables.LEVEL + 1
FROM Executables CROSS APPLY
Executables.ChildNodes.nodes('/DTS:Executable') AS V(Vars)
) -- CTE - Recursive query of Executables in Package XML
SELECT *
FROM Executables
ORDER BY PackageName, LEVEL
-- Drop the temp table
DROP TABLE #t