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.

Using XML Data Type Methods to query SSIS Packages

So a few weeks ago I was doing some work on SSIS Packages and I needed a quick way to get some information from the package. Although the Visual Studio (AKA SQL BI Dev Studio) Package Designer is nice, it can be cumbersome to fetch various pieces of information, requiring the opening and closing of various windows and/or toolboxes, and it doesn't allow you to copy certain lists, such as variables. So, knowing the package itself is stored as XML, I wondered if I could use SQL Server 2005's XML features to write some queries to get the information I needed.

The final result: I was able to fetch the package configuration information, variables, properties, and a hierarchy of executables in the package. Here is the code I used to do these tasks:

-- Variables and temp tables
DECLARE @x xml
CREATE
TABLE #t (PackageName varchar(100), PackageCode xml)

-- Bulk load the Package XML into @x
SET @X = (SELECT * FROM OPENROWSET
(BULK N'C:\Path To Your Package\PackageName.dtsx',
SINGLE_BLOB
) AS Document)

-- Load the @x into the temp table
INSERT INTO #t VALUES ('PackageName.dtsx', @x)

-- Query Package Configurations
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as p1,
  'www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
SELECT PackageName,
  V.Vars.value('./p1:Property [@p1:Name="ConfigurationType"][1]',
    'varchar(100)') AS ConfigurationType,
  CASE CAST(V.Vars.value('./p1:Property[@p1: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,
  V.Vars.value('./p1:Property[@p1:Name="ConfigurationVariable"][1]',
    'varchar(100)') AS ConfigurationVariable,
  V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]',
    'varchar(100)') AS ConfigurationName,
  V.Vars.value('./p1:Property[@p1:Name="ConfigurationString"][1]',
   
'varchar(100)') AS ConfigurationString
FROM #t CROSS APPLY #t.PackageCode.nodes('/DTS:Executable/DTS:Configuration') AS V(Vars)

-- Query Package Variables
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as p1,
  'www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
SELECT PackageName,
  V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]',
    'varchar(100)') AS VariableName
FROM #t CROSS APPLY
  #t.PackageCode.nodes('/DTS:Executable/DTS:Variable') AS V(Vars)

-- Query Package Properties
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as p1,
  'www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
SELECT PackageName,
  V.Vars.value('attribute::DTS:Name', 'varchar(100)') AS PropertyName,
  V.Vars.value('.', 'varchar(100)') AS PropertyValue
FROM #t CROSS APPLY 
  #t.PackageCode.nodes('/DTS:Executable/DTS:Property') AS V(Vars)

-- Query Package Executable Hierarchy
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as p1,
'www.microsoft.com/SqlServer/Dts' as DTS) -- CTE to declare XML namespaces
, Executables AS
  (SELECT PackageName,
    CAST('' as varchar(100)) AS ParentNodeName,
    V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]',
      'varchar(100)') AS NodeName,
    V.Vars.query('.') AS CurrentNode,
    V.Vars.query('./p1:Executable') AS ChildNodes,
    V.Vars.value('./p1:Property[@p1:Name="ConfigurationType"][1]',
      'varchar(100)') AS ConfigurationType,
    1 AS Level
  FROM #t CROSS APPLY #t.PackageCode.nodes('/DTS:Executable') AS V(Vars)

  UNION ALL

  SELECT PackageName,
    Executables.NodeName AS ParentNodeName,
    V.Vars.value('./p1:Property[@p1:Name="ObjectName"][1]',
      'varchar(100)') AS NodeName,
    V.Vars.query('.') AS CurrentNode,
    V.Vars.query('./p1:Executable') AS ChildNodes,
    V.Vars.value('./p1:Property[@p1:Name="ConfigurationType"][1]',
      'varchar(100)') AS ConfigurationType,
    Executables.Level + 1
  FROM Executables CROSS APPLY 
    Executables.ChildNodes.nodes('/DTS:Executable') AS V(Vars)
  ) -- CTE - Recursive query of Executables in Package XML
SELECT *
FROM Executables
ORDER BY PackageName, Level

-- Drop the temp table
DROP TABLE #t

This is just the tip of the iceburg and I'm sure other folks will come up with some other cool (yes, I am a geek) queries to get even more information from the packages using SQL Server 2005's XML features.

Published Thursday, July 13, 2006 10:26 AM by Peter W. DeBetta

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

 

Peter DeBetta's SQL Programming Blog said:

This is a follow-up to a posting from several months ago (Using XML Data Type Methods to query SSIS...
December 18, 2006 3:10 PM
 

Barb Eichberger said:

This is dated 2006, but it sure helped me to build some docuementation around SSIS packages.  There's a codeplex documenting item (Metadata Toolkit) available for 2008 R2 that attempts to aid with SSIS documentation; has a graphical display for dependencies so look neater than it is.  I think they are missing the boat a bit on useful searchable documentation.  Anyway, just wanted to let you  know that I've used your work and really appreciate it.

September 20, 2011 12:23 PM
 

Jude McBride said:

Peter - you may call yourself a 'geek' but this is genious and kinda exactly what I was looking for.

My SSIS package has hundreds of transformations in Derive coloum and I need to show the client a clean list of them. Aint no other way other than this.

Thanks a million ! Keep up the good work.

Jude

December 19, 2011 7:02 AM

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