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.