THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

XML Data Type Methods and SSIS Package Variables

This is a follow-up to a posting from several months ago (Using XML Data Type Methods to query SSIS Packages). I included a temp table (based on information I found in the Microsoft.SqlServer.Dts.Runtime.VarTypeConvert class's constructor) which gives the conversion of the numeric data type value to the appropriate TypeCode (System.TypeCode). Thank goodness for Lutz Roeder's Reflector. This code iterates through all the executables in the package and for all variables returns the following properties: Executable Scope, Name, Namespace, Value, DataType (and data type description), EvaluateAsExpression, Expression, ReadOnly, RaiseChangedEvent, DTSID, Description, and CreationName. I am waiting on some confirmation about the property data types themselves (string lengths in particular) and will follow up as soon as I know more. In the meantime, here is the code. Be sure to change the package path to the location of your package file before running...

DECLARE @x xml

CREATE TABLE #t (PackageName varchar(100), PackageCode xml)

SET @X = (SELECT * FROM OPENROWSET
   (BULK N'C:\Projects\MyPackage.dtsx',

   SINGLE_BLOB) AS Document)

INSERT INTO #t VALUES ('MyPackage.dtsx', @x)

CREATE TABLE #VarTypeCode (TypeID int, TypeName varchar(20))

INSERT INTO #VarTypeCode VALUES(0, 'Empty')
INSERT INTO #VarTypeCode VALUES(1, 'DBNull'
)
INSERT INTO #VarTypeCode VALUES(2, 'Int16'
)
INSERT INTO #VarTypeCode VALUES(3, 'Int32'
)
INSERT INTO #VarTypeCode VALUES(4, 'Single'
)
INSERT INTO #VarTypeCode VALUES(5, 'Double'
)
INSERT INTO #VarTypeCode VALUES(6, 'Decimal'
)
INSERT INTO #VarTypeCode VALUES(7, 'DateTime'
)
INSERT INTO #VarTypeCode VALUES(8, 'String'
)
INSERT INTO #VarTypeCode VALUES(9, 'Object'
)
INSERT INTO #VarTypeCode VALUES(10, 'Object'
)
INSERT INTO #VarTypeCode VALUES(11, 'Boolean'
)
INSERT INTO #VarTypeCode VALUES(12, 'Object'
)
INSERT INTO #VarTypeCode VALUES(13, 'Object'
)
INSERT INTO #VarTypeCode VALUES(14, 'Decimal'
)
INSERT INTO #VarTypeCode VALUES(16, 'SByte'
)
INSERT INTO #VarTypeCode VALUES(17, 'Byte'
)
INSERT INTO #VarTypeCode VALUES(18, 'UInt16'
)
INSERT INTO #VarTypeCode VALUES(19, 'UInt32'
)
INSERT INTO #VarTypeCode VALUES(20, 'Int64'
)
INSERT INTO #VarTypeCode VALUES(21, 'UInt64'
)
INSERT INTO #VarTypeCode VALUES(22, 'Int16'
)
INSERT INTO #VarTypeCode VALUES(23, 'UInt16'
)
INSERT INTO #VarTypeCode VALUES(24, 'Empty'
)

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as p1,
   
'www.microsoft.com/SqlServer/Dts' as DTS
)
,
Executables
AS
(   SELECT CAST('' as varchar(100)) AS ParentNodeName
,
     
P.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]', 'varchar(100)') AS NodeName
,
     
P.Vars.query('.') AS CurrentNode
,
     
P.Vars.query('./p1:Executable') AS ChildNodes
,
     
1 AS
Level
   FROM
#t
      CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS P(Vars)

   UNION ALL

   SELECT Executables.NodeName AS ParentNodeName,
     
P.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]', 'varchar(100)') AS NodeName
,
     
P.Vars.query('.') AS CurrentNode
,
     
P.Vars.query('./p1:Executable') AS ChildNodes
,
     
Executables.Level +
1
   FROM
Executables
      CROSS APPLY Executables.ChildNodes.nodes('/DTS:Executable') AS P(Vars
)
)
,
Variables AS
(  
SELECT Executables.NodeName, Executables.Level
,
     
V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]', 'varchar(255)') AS [Name]
,
     
V.Vars.value('./p1:Property[@p1:Name="Namespace"][1]', 'varchar(255)') AS Namespace
,
     
V.Vars.value('./p1:VariableValue[1]', 'varchar(1024)') AS [Value]
,
     
V.Vars.value('./p1:VariableValue[1]/@p1:DataType', 'int') AS DataType
,
     
V.Vars.value('./p1:Property[@p1:Name="Expression"][1]', 'varchar(1024)') AS Expression
,
     
V.Vars.value('./p1:Property[@p1:Name="EvaluateAsExpression"][1]', 'bit') AS EvaluateAsExpression
,
     
V.Vars.value('./p1:Property[@p1:Name="ReadOnly"][1]', 'bit') AS ReadOnly
,
     
V.Vars.value('./p1:Property[@p1:Name="RaiseChangedEvent"][1]', 'bit') AS RaiseChangedEvent
,
     
V.Vars.value('./p1:Property[@p1:Name="DTSID"][1]', 'uniqueidentifier') AS DTSID
,
     
V.Vars.value('./p1:Property[@p1:Name="Description"][1]', 'varchar(255)') AS Description
,
     
V.Vars.value('./p1:Property[@p1:Name="CreationName"][1]', 'varchar(255)') AS CreationName
  
FROM Executables
      CROSS APPLY Executables.CurrentNode.nodes('/p1:Executable/p1:Variable') AS V(Vars
)
)
SELECT V.NodeName AS ExecutableName, V.Level, V.[Name],
   V.Namespace, V.[Value], V.DataType, TCC.TypeName
,
   
V.Expression, V.EvaluateAsExpression, V.ReadOnly,
   V.RaiseChangedEvent,
V.DTSID, V.Description, V.CreationName
FROM
Variables AS V
  
INNER JOIN #VarTypeCode AS TCC ON V.DataType = TCC.TypeID
ORDER BY Level, NodeName, [Name]

DROP TABLE #t

DROP TABLE #VarTypeCode

--Peter

Published Monday, December 18, 2006 2:31 PM by Peter W. DeBetta
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement