1 XML 로딩

2 Query Package Base

3 Query Package Properties

4 Query Package Variables

5 Query Package Executable Hierarchy

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;