Documenting SSIS Packages

In the last post, we looked at query options for documenting Maintenance Plans. I didn't expect that to get too many of you excited. But maybe a post about SSIS packages will. The techniques used in the last post can be applied with SSIS packages that are deployed via the legacy package deployment model.

As with their Maintenance Plan counterparts, these SSIS packages "live" in the [msdb] system database, and the data that comprises each package is XML. This query returns one row for each package:

SELECT p.name AS Package_Name, p.description AS Package_Description, f.foldername, 
 CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS XML) AS Ssis_Package_Xml
FROM msdb.dbo.sysssispackages p
INNER JOIN msdb.dbo.sysssispackagefolders AS f 
 ON f.folderid = p.folderid
WHERE p.packagetype = 5 --Packages created by SSIS designer.

For this post, I'll be using a sample SSIS package from Microsoft's GitHub repository. I won't include the XML inline with this post, so feel free to peruse the source code. Let's look at the package in the Visual Studio GUI before we proceed further.

The package name is "DailyETLMain" and it contains numerous Sequence containers and a few other tasks. Expanding any of the Sequence containers show us the subtasks within.

XML Shredding

Below is the query to shred the XML of each SSIS package returned by the first query (above). Without getting too deep into the XML query syntax, note the CROSS APPLY uses the .nodes() method to find the top-level Executable objects of each SSIS package ('/DTS:Executable/DTS:Executables/DTS:Executable').

WITH XMLNAMESPACES (
 'www.microsoft.com/SqlServer/Dts' AS DTS,
 'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
SELECT p.Package_Name, p.foldername, 
 tasks.value('(../../DTS:Property[@DTS:Name="PackageFormatVersion"][1])', 'SMALLINT') AS PackageFormatVersion,
 tasks.value('(../../@DTS:ObjectName)', 'VARCHAR(4000)') AS XmlPackageName,
 tasks.value('(../../@DTS:Description)', 'VARCHAR(4000)') AS XmlPackageDescription,

 tasks.value('(@DTS:ObjectName)', 'VARCHAR(4000)') AS TaskName,
 tasks.value('(@DTS:Description)', 'VARCHAR(4000)') AS TaskDescription
FROM
(
 SELECT p.name AS Package_Name, p.description AS Package_Description, f.foldername, 
  CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS XML) AS Ssis_Package_Xml
 FROM msdb.dbo.sysssispackages p
 INNER JOIN msdb.dbo.sysssispackagefolders AS f 
  ON f.folderid = p.folderid
 WHERE p.packagetype = 5 --Packages created by SSIS designer.
) p
CROSS APPLY p.Ssis_Package_Xml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable') AS t(tasks) 

The output is a tabular result showing each SSIS package, their Names and Descriptions, and the top-level task Names and Descriptions of each subplan. (In addition to the "DailyETLMain" package, we see metadata for some other plans related to the Management Data Warehouse Data Collector.) Note the 16 rows of metadata for the "DailyETLMain" package correspond to the 16 top-level objects of the package--the query doesn't recurse into containers to obtain their object metadata. I decided not to attempt that--it seemed like overkill for documentation purposes. Another caveat: the results order may not match the order that's mandated by Precedence Constraints in the Visual Studio designer.

As developers, we have full control over all of the metadata for each package and its objects. Within Visual Studio, right-click an object to open the Properties window. Here you can change the Name and Description (among others). I like "Load Transaction Fact" for the object Name. But "Sequence Container" for the Description? Surely we can do better than that!

SSIS Package Format

The XML format can vary, depending on the version of SQL Server (I'm not certain, but I think it depends directly on the version of SQL Server where the system table msdb.dbo.sysssispackages resides). The example I used is from SQL Server 2019, and the package format version is 8, as noted in this part of the XML data:

<DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>

The main query above seems to work for PackageFormatVersion 6 (SQL Server 2012) as well as for PackageFormatVersion 8 (SQL 2014 and above). For PackageFormatVersion 3 (SQL Server 2008 R2), the query needs to be slightly modified to reflect the different XML data structure:

WITH XMLNAMESPACES (
 'www.microsoft.com/SqlServer/Dts' AS DTS,
 'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
SELECT p.Package_Name, p.foldername, p.Package_Description, 
 tasks.value('(../DTS:Property[@DTS:Name="PackageFormatVersion"][1])', 'SMALLINT') AS PackageFormatVersion,
 tasks.value('(../DTS:Property[@DTS:Name="ObjectName"])[1]', 'VARCHAR(4000)') AS XmlPackageName,
 tasks.value('(../DTS:Property[@DTS:Name="Description"])[1]', 'VARCHAR(4000)') AS XmlPackageDescription,

 tasks.value('(DTS:Property[@DTS:Name="ObjectName"][1])', 'VARCHAR(4000)')  AS TaskName,
 tasks.value('(DTS:Property[@DTS:Name="Description"][1])', 'VARCHAR(4000)')  AS TaskDescription
FROM
(
 SELECT p.name AS Package_Name, p.description AS Package_Description, f.foldername, 
  CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS XML) AS Ssis_Package_Xml
 FROM msdb.dbo.sysssispackages p
 INNER JOIN msdb.dbo.sysssispackagefolders AS f 
  ON f.folderid = p.folderid
 WHERE p.packagetype = 5 --SSIS designer types
) p
CROSS APPLY p.Ssis_Package_Xml.nodes('/DTS:Executable/DTS:Executable') AS t(tasks) 

Have you moved on from the package deployment model? Maybe you're wondering how to access the metadata of SSIS packages deployed to an SSIS catalog via the project deployment model. Hmmm. It doesn't look like we can use T-SQL for that. But there are other ways...