DROP TABLE #t -- Variables and temp tables DECLARE @x xml CREATE TABLE #t (PackageCode xml) -- Bulk load the Package XML into @x SET @x = (SELECT * FROM OPENROWSET (BULK N'C:\Package1.dtsx', SINGLE_BLOB) AS Document) -- Load the @x into the temp table INSERT INTO #t VALUES (@x)
;WITH XMLNAMESPACES('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
SELECT
x.items.value('./DTS:Property[@DTS:Name="VersionGUID"][1]', 'uniqueidentifier ') AS PackageID
, x.items.value('./DTS:Property[@DTS:Name="ObjectName"][1]', 'varchar(255)') AS PackageName
, x.items.value('./DTS:Property[@DTS:Name="CreationDate"][1]', 'datetime') AS CreationDate
, x.items.value('./DTS:Property[@DTS:Name="ProtectionLevel"][1]', 'int') AS ProtectionLevel
, x.items.value('./DTS:Property[@DTS:Name="PackageType"][1]', 'int') AS PackageType
, x.items.value('./DTS:Property[@DTS:Name="CreatorName"][1]', 'varchar(255)') AS CreatorName
, x.items.value('./DTS:Property[@DTS:Name="CreatorComputerName"][1]', 'varchar(255)') AS CreatorComputerName
FROM #t
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS x(items)
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
SELECT
y.items.value('./DTS:Property[@DTS:Name="VersionGUID"][1]', 'uniqueidentifier') AS PackageID
, x.items.value('attribute::DTS:Name', 'varchar(500)') AS PropertyName
, x.items.value('.', 'varchar(500)') AS PropertyValue
FROM #t
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable/DTS:Property') AS x(items)
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS y(items)
-- Query Package Configurations
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
SELECT
y.items.value('./DTS:Property[@DTS:Name="VersionGUID"][1]', 'uniqueidentifier') AS PackageID
, x.items.value('./DTS:Property [@DTS:Name="ConfigurationType"][1]', 'varchar(100)') AS ConfigurationType
, CASE CAST(x.items.value('./DTS:Property[@DTS: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
, x.items.value('./DTS:Property[@DTS:Name="ConfigurationVariable"][1]', 'varchar(100)') AS ConfigurationVariable
, x.items.value('./DTS:Property[@DTS:Name="ObjectName"][1]', 'varchar(100)') AS ConfigurationName
, x.items.value('./DTS:Property[@DTS:Name="ConfigurationString"][1]', 'varchar(100)') AS ConfigurationString
FROM #t
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable/DTS:Configuration') AS x(items)
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS y(items)
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
SELECT
y.items.value('./DTS:Property[@DTS:Name="VersionGUID"][1]', 'uniqueidentifier') AS PackageID
, x.items.value('./DTS:Property[@DTS:Name="ObjectName"][1]', 'varchar(100)') AS VariableName
, x.items.value('(./DTS:VariableValue/@DTS:DataType)[1]', 'varchar(100)') AS DataType
FROM #t
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable/DTS:Variable') AS x(items)
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS y(items)
-- Query Package ConnectionManager
;WITH XMLNAMESPACES('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
, Base(PackageID, ConnectionID, ObjectName, Descriptions, ConnectionType, ConnStr)
AS
(
SELECT
y.items.value('./DTS:Property[@DTS:Name="VersionGUID"][1]', 'uniqueidentifier') AS PackageID
, x.items.value('./DTS:Property[@DTS:Name="DTSID"][1]', 'uniqueidentifier ') AS ConnectionID
, x.items.value('./DTS:Property[@DTS:Name="ObjectName"][1]', 'varchar(255)') AS ObjectName
, x.items.value('./DTS:Property[@DTS:Name="Description"][1]', 'varchar(255)') AS Descriptions
, x.items.value('./DTS:Property[@DTS:Name="CreationName"][1]', 'varchar(255)') AS ConnectionType
, x.items.query('./DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ConnectionString"]') AS ConnStr
FROM #t
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable/DTS:ConnectionManager') AS x(items)
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS y(items)
)
SELECT
PackageID
, ConnectionID
, ObjectName
, ConnectionType
, ConnStr.value('./DTS:Property[@DTS:Name="ConnectionString"][1]', 'varchar(255)') AS ConnectionString
, Descriptions
FROM Base
;WITH XMLNAMESPACES('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
, Executables AS
(
SELECT
x.items.value('./DTS:Property[@DTS:Name="DTSID"][1]', 'uniqueidentifier') AS ParentNodeID
, CAST('' as varchar(255)) AS ParentObjectName
, x.items.value('./DTS:Property[@DTS:Name="DTSID"][1]', 'uniqueidentifier') AS NodeID
, x.items.value('./DTS:Property[@DTS:Name="ObjectName"][1]', 'varchar(255)') AS ObjectName
, x.items.value('./DTS:Property[@DTS:Name="Description"][1]', 'varchar(255)') AS Descriptions
, x.items.query('./DTS:Executable') AS ChildNodes
, 1 AS Level
, CAST(x.items.value('./DTS:Property[@DTS:Name="ObjectName"][1]', 'varchar(255)') as varchar(max)) OrderPath
FROM #t CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS x(items)
UNION ALL
SELECT
Executables.NodeID AS ParentNodeID
, Executables.ObjectName AS ParentObjectName
, x.items.value('./DTS:Property[@DTS:Name="DTSID"][1]', 'uniqueidentifier') AS NodeID
, x.items.value('./DTS:Property[@DTS:Name="ObjectName"][1]', 'varchar(255)') AS ObjectName
, x.items.value('./DTS:Property[@DTS:Name="Description"][1]', 'varchar(255)') AS Descriptions
, x.items.query('./DTS:Executable') AS ChildNodes
, Executables.Level + 1
, CAST(Executables.OrderPath + ' -> ' + x.items.value('./DTS:Property[@DTS:Name="ObjectName"][1]', 'varchar(255)') as varchar(max)) OrderPath
FROM Executables CROSS APPLY
Executables.ChildNodes.nodes('/DTS:Executable') AS x(items)
), -- CTE - Recursive query of Executables in Package XML
Result AS
(
SELECT
ParentNodeID ParentNodeID
, ParentObjectName
, NodeID
, ChildNodes
, REPLICATE(' ', Level-1) + ObjectName ObjectName
, Descriptions
, OrderPath
, Level
FROM Executables
)
SELECT * INTO #Result
FROM Result
SELECT TOP 0 * INTO #temp FROM #Result
DECLARE
@id uniqueidentifier
, @max int
, @lv int
, @root uniqueidentifier;
SET @max = (SELECT MAX(Level) FROM #Result)
DECLARE cur CURSOR FOR
SELECT NodeID, Level
FROM #Result
WHERE Level < @max
OPEN cur;
FETCH NEXT FROM cur INTO @id, @lv;
WHILE @@FETCH_STATUS not in (-1, -2)
BEGIN
INSERT #temp
SELECT * FROM #Result
WHERE NodeID = @id
AND Level < @max
INSERT #temp
SELECT * FROM #Result
WHERE ParentNodeID = @id
AND Level >= @max
IF @lv = 1
BEGIN
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
SELECT
@root = y.items.value('./DTS:Property[@DTS:Name="VersionGUID"][1]', 'uniqueidentifier')
FROM #t
CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS y(items)
END
FETCH NEXT FROM cur INTO @id, @lv;
END
SELECT
@root PackageID
, ParentNodeID
, NodeID
, ObjectName
, Descriptions
, OrderPath
, Level
, ChildNodes
, identity(int,1,1) Seq
INTO #
FROM #temp
INSERT dbo.SSIS_Package_Hierarchy(PackageID, ParentNodeID, NodeID, ObjectName, Descriptions, OrderPath, Level, Seq)
SELECT
PackageID
, ParentNodeID
, NodeID
, ObjectName
, Descriptions
, OrderPath
, [Level]
, Seq
FROM #
DROP TABLE #temp
DROP TABLE #Result
CLOSE cur;
DEALLOCATE cur;