DataBaser.Net: .dtsx 파일 구조분석

1 테이블 만들기
2 구조를 까기

2016 버전까지는 잘 먹힌다.

/*
create table #temp
(
    package_name nvarchar(500)
,   xml_data xml
)
*/
--종속성
;with xmlnamespaces(
    'www.microsoft.com/SqlServer/Dts' as DTS
,   'www.microsoft.com/sqlserver/dts/tasks/sqltask' as SQLTask
)
select 
    package_name
,   task.items.value('@DTS:DTSID', 'uniqueidentifier') as package_id
,   task.items.value('@DTS:From', 'nvarchar(500)') as from_refId
,   task.items.value('@DTS:To', 'nvarchar(500)') as to_refId
,   task.items.value('@DTS:Value', 'int') as value
from #temp a
	cross apply a.xml_data.nodes('/DTS:Executable//DTS:PrecedenceConstraint') as task(items)


--Connection
;with xmlnamespaces(
    'www.microsoft.com/SqlServer/Dts' as DTS
,   'www.microsoft.com/sqlserver/dts/tasks/sqltask' as SQLTask
)
select 
    package_name
,   pkg_info.items.value('@DTS:DTSID', 'uniqueidentifier') as package_id
,   pkg_info.items.value('@DTS:refId', 'nvarchar(500)') as package_refId
,   conn_info.items.value('@refId', 'nvarchar(500)') as connection_ref_id
,   conn_info.items.value('@connectionManagerID', 'uniqueidentifier') as connection_manager_id
,   conn_info.items.value('@connectionManagerRefId', 'nvarchar(500)') as connection_manager_refid
from #temp a
	cross apply a.xml_data.nodes('/DTS:Executable') as pkg_info(items)
	outer apply pkg_info.items.nodes('//connection') as conn_info(items)



--흐름
;with xmlnamespaces(
    'www.microsoft.com/SqlServer/Dts' as DTS
,   'www.microsoft.com/sqlserver/dts/tasks/sqltask' as SQLTask
)
select 
    package_name
,   pkg_info.items.value('@DTS:DTSID', 'uniqueidentifier') as package_id
,   pkg_info.items.value('@DTS:refId', 'nvarchar(500)') as package_refId
,   executable.items.value('@DTS:refId', 'nvarchar(500)') as ref_id
,   executable.items.value('@DTS:ObjectName', 'nvarchar(500)') as object_name
,   executable.items.value('@DTS:ExecutableType', 'nvarchar(500)') as executable_type
,   executable.items.value('@DTS:Description', 'nvarchar(255)') as description
,   component.items.value('@refId', 'nvarchar(500)') component_ref_id
,   component.items.value('@name', 'nvarchar(500)') component_name
,   component.items.value('@componentClassID', 'nvarchar(500)') component_class_id
,   isnull(
        sqltaskdata.items.value('@SQLTask:Connection', 'uniqueidentifier'),
        component_connection.items.value('@connectionManagerID', 'uniqueidentifier')
    ) connection_manager_id
,   component.items.value('(properties/property[@name="SqlCommand"])[1]', 'nvarchar(max)') sql_command
from #temp a
	cross apply a.xml_data.nodes('/DTS:Executable') as pkg_info(items)
	outer apply pkg_info.items.nodes('DTS:Executables//DTS:Executable') as executable(items)
	outer apply executable.items.nodes('DTS:ObjectData/SQLTask:SqlTaskData') as sqltaskdata(items)
	outer apply executable.items.nodes('DTS:ObjectData/pipeline/components//component') as component(items)
	outer apply component.items.nodes('connections/connection') as component_connection(items)
order by ref_id, component_ref_id

--아래꺼는 옛날 버전이다.

[edit]

1 테이블 만들기 #

다음과 같은 구조로 테이블을 만든다.

소스는 다음과 같다.

/****** 개체:  Table [dbo].[SSIS_Package_Base]    스크립트 날짜: 08/12/2009 08:33:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Base]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SSIS_Package_Base](
	[PackageID] [uniqueidentifier] NOT NULL,
	[PackageName] [varchar](255) NULL,
	[PackageFileName] [varchar](1000) NULL,
	[PackageFile] [xml] NULL,
	[CreationDate] [datetime] NULL,
	[ProtectionLevel] [int] NULL,
	[PackageType] [int] NULL,
	[CreatorName] [varchar](255) NULL,
	[CreatorComputerName] [varchar](255) NULL,
	[ServerName] [varchar](255) NULL,
 CONSTRAINT [PK_SSIS_Package_Base] PRIMARY KEY CLUSTERED 
(
	[PackageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** 개체:  Table [dbo].[SSIS_Package_Hierarchy]    스크립트 날짜: 08/12/2009 08:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Hierarchy]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SSIS_Package_Hierarchy](
	[PackageID] [uniqueidentifier] NOT NULL,
	[ParentNodeID] [uniqueidentifier] NOT NULL,
	[NodeID] [uniqueidentifier] NOT NULL,
	[ObjectName] [varchar](255) NULL,
	[Descriptions] [varchar](255) NULL,
	[OrderPath] [varchar](255) NULL,
	[Level] [varchar](255) NULL,
	[Seq] [int] NULL,
	[ChildNodes] [xml] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** 개체:  Table [dbo].[SSIS_Package_Connection]    스크립트 날짜: 08/12/2009 08:34:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Connection]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SSIS_Package_Connection](
	[PackageID] [uniqueidentifier] NOT NULL,
	[ConnectionID] [varchar](255) NOT NULL,
	[ObjectName] [varchar](255) NULL,
	[ConnectionType] [varchar](255) NULL,
	[ConnectionString] [varchar](500) NULL,
	[Descriptions] [varchar](255) NULL,
 CONSTRAINT [PK_SSIS_Package_Connection] PRIMARY KEY CLUSTERED 
(
	[PackageID] ASC,
	[ConnectionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** 개체:  Table [dbo].[SSIS_Package_Variables]    스크립트 날짜: 08/12/2009 08:34:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Variables]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SSIS_Package_Variables](
	[PackageID] [uniqueidentifier] NOT NULL,
	[VariableName] [varchar](100) NOT NULL,
	[DataType] [varchar](100) NULL,
 CONSTRAINT [PK_SSIS_Package_Variables] PRIMARY KEY CLUSTERED 
(
	[PackageID] ASC,
	[VariableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** 개체:  Table [dbo].[SSIS_Package_Config]    스크립트 날짜: 08/12/2009 08:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Config]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SSIS_Package_Config](
	[PackageID] [uniqueidentifier] NOT NULL,
	[ConfigurationType] [varchar](100) NOT NULL,
	[ConfigurationTypeDesc] [varchar](100) NULL,
	[ConfigurationVariable] [varchar](100) NULL,
	[ConfigurationName] [varchar](100) NULL,
	[ConfigurationString] [varchar](100) NULL,
 CONSTRAINT [PK_SSIS_Package_Config] PRIMARY KEY CLUSTERED 
(
	[PackageID] ASC,
	[ConfigurationType] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** 개체:  Table [dbo].[SSIS_Package_Properties]    스크립트 날짜: 08/12/2009 08:34:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Properties]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SSIS_Package_Properties](
	[PackageID] [uniqueidentifier] NOT NULL,
	[PropertyName] [varchar](500) NOT NULL,
	[PropertyValue] [varchar](500) NULL,
 CONSTRAINT [PK_SSIS_Package_Properties] PRIMARY KEY CLUSTERED 
(
	[PackageID] ASC,
	[PropertyName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** 개체:  ForeignKey [FK_SSIS_Package_Config_SSIS_Package_Base]    스크립트 날짜: 08/12/2009 08:33:59 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[FK_SSIS_Package_Config_SSIS_Package_Base]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Config]'))
ALTER TABLE [dbo].[SSIS_Package_Config]  WITH CHECK ADD  CONSTRAINT [FK_SSIS_Package_Config_SSIS_Package_Base] FOREIGN KEY([PackageID])
REFERENCES [dbo].[SSIS_Package_Base] ([PackageID])
GO
ALTER TABLE [dbo].[SSIS_Package_Config] CHECK CONSTRAINT [FK_SSIS_Package_Config_SSIS_Package_Base]
GO
/****** 개체:  ForeignKey [FK_SSIS_Package_Connection_SSIS_Package_Base]    스크립트 날짜: 08/12/2009 08:34:02 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[FK_SSIS_Package_Connection_SSIS_Package_Base]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Connection]'))
ALTER TABLE [dbo].[SSIS_Package_Connection]  WITH CHECK ADD  CONSTRAINT [FK_SSIS_Package_Connection_SSIS_Package_Base] FOREIGN KEY([PackageID])
REFERENCES [dbo].[SSIS_Package_Base] ([PackageID])
GO
ALTER TABLE [dbo].[SSIS_Package_Connection] CHECK CONSTRAINT [FK_SSIS_Package_Connection_SSIS_Package_Base]
GO
/****** 개체:  ForeignKey [FK_SSIS_Package_Hierarchy_SSIS_Package_Base]    스크립트 날짜: 08/12/2009 08:34:07 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[FK_SSIS_Package_Hierarchy_SSIS_Package_Base]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Hierarchy]'))
ALTER TABLE [dbo].[SSIS_Package_Hierarchy]  WITH CHECK ADD  CONSTRAINT [FK_SSIS_Package_Hierarchy_SSIS_Package_Base] FOREIGN KEY([PackageID])
REFERENCES [dbo].[SSIS_Package_Base] ([PackageID])
GO
ALTER TABLE [dbo].[SSIS_Package_Hierarchy] CHECK CONSTRAINT [FK_SSIS_Package_Hierarchy_SSIS_Package_Base]
GO
/****** 개체:  ForeignKey [FK_SSIS_Package_Properties_SSIS_Package_Base]    스크립트 날짜: 08/12/2009 08:34:09 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[FK_SSIS_Package_Properties_SSIS_Package_Base]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Properties]'))
ALTER TABLE [dbo].[SSIS_Package_Properties]  WITH CHECK ADD  CONSTRAINT [FK_SSIS_Package_Properties_SSIS_Package_Base] FOREIGN KEY([PackageID])
REFERENCES [dbo].[SSIS_Package_Base] ([PackageID])
GO
ALTER TABLE [dbo].[SSIS_Package_Properties] CHECK CONSTRAINT [FK_SSIS_Package_Properties_SSIS_Package_Base]
GO
/****** 개체:  ForeignKey [FK_SSIS_Package_Variables_SSIS_Package_Base]    스크립트 날짜: 08/12/2009 08:34:12 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[FK_SSIS_Package_Variables_SSIS_Package_Base]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[SSIS_Package_Variables]'))
ALTER TABLE [dbo].[SSIS_Package_Variables]  WITH CHECK ADD  CONSTRAINT [FK_SSIS_Package_Variables_SSIS_Package_Base] FOREIGN KEY([PackageID])
REFERENCES [dbo].[SSIS_Package_Base] ([PackageID])
GO
ALTER TABLE [dbo].[SSIS_Package_Variables] CHECK CONSTRAINT [FK_SSIS_Package_Variables_SSIS_Package_Base]
GO

[edit]

2 구조를 까기 #

.dtsx 파일의 구조를 까기 위해서 다음의 프로시저를 만든다. 해석은 알아서.. (저도 대충 만들어서리.. ㅋㅋ)

USE [admin]
GO
/****** Object:  StoredProcedure [dbo].[SSIS_XML_Parsing]    Script Date: 2011-12-06 오후 4:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SSIS_XML_Parsing]
	@server varchar(255)
,	@file_name varchar(1000)
,	@x xml
AS
SET NOCOUNT ON 
SET STATISTICS IO OFF
set ANSI_PADDING on

BEGIN
	SELECT @x PackageCode
	INTO #t;

	-- Query Package Base
	WITH XMLNAMESPACES('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
	INSERT dbo.SSIS_Package_Base(PackageID, PackageName, PackageFileName, PackageFile, CreationDate, ProtectionLevel, PackageType, CreatorName, CreatorComputerName, ServerName)
	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
	,	@file_name PackageFileName
	,	@x PackageFile
	,	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
	,	@server ServerName
	FROM #t
		CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS x(items);

	-- Query Package Properties
	WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
	INSERT dbo.SSIS_Package_Properties(PackageID, PropertyName, PropertyValue)
	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
	INSERT dbo.SSIS_Package_Config(PackageID, ConfigurationType, ConfigurationTypeDesc, ConfigurationVariable, ConfigurationName, ConfigurationString)
    select
        PackageID, ConfigurationType
    ,   min(ConfigurationTypeDesc)
	,	min(ConfigurationVariable)
	,	min(ConfigurationName)
	,	min(ConfigurationString)
    from (
	    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)
    ) t
    group by
        PackageID, ConfigurationType
    ;

	-- Query Package Variables
	WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
	INSERT dbo.SSIS_Package_Variables(PackageID, VariableName, DataType)
	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)
	)
	INSERT dbo.SSIS_Package_Connection(PackageID, ConnectionID, ObjectName, ConnectionType, ConnectionString, Descriptions)
	SELECT 
		PackageID
	,	ConnectionID
	,	ObjectName
	,	ConnectionType
	,	ConnStr.value('./DTS:Property[@DTS:Name="ConnectionString"][1]', 'varchar(255)') AS ConnectionString
	,	Descriptions
	FROM Base

	-- Query Package Executable Hierarchy
	;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, ChildNodes)
	SELECT
		PackageID
	,	ParentNodeID
	,	NodeID
	,	ObjectName
	,	Descriptions
	,	OrderPath
	,	[Level]
	,	Seq
	,	ChildNodes
	FROM #

	DROP TABLE #temp
	DROP TABLE #Result
	CLOSE cur;
	DEALLOCATE cur;
END
GO

이렇게 검색하면 된다.

select
    a.PackageName
,   b.ObjectName
,   b.OrderPath
from dbo.SSIS_Package_Base a
    inner join dbo.SSIS_Package_Hierarchy b
        on a.PackageID = b.PackageID
order by a.PackageName, b.seq

Contents

1 테이블 만들기 #

2 구조를 까기 #