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
鏤