There is already an object named 'RSExecRole' in the database

Dave Mason - SSRS

When migrating an instance of SSRS, I performed a backup of the [ReportServer] and [ReportServerTemp] SSRS databases from a SQL Server 2008 R2 instance and restored them to a SQL Server 2017 instance. After installing SSRS 2017 on the target machine, I ran SSRS configuration and attempted to set the Current Report Server Database to the existing [ReportServer] database I had already restored:

Dave Mason - Report Server Configuration Manager

Dave Mason - SSRS Change Database

Dave Mason - SSRS Change Database


At the last step (Progress and Finish), here is the text of the error message:

System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'RSExecRole' in the database.
CREATE SCHEMA failed due to previous errors.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
   at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
ClientConnectionId:4f47a341-9eca-4963-bfcd-ba707d8882a0
Error Number:2714,State:6,Class:16

I was somewhat familiar with the RSExecRole database role. Knowing it had to exist for SSRS to function, I was initially puzzled. After running an Extended Events session for the Error_Reported event, I found this SQL statement that was failing in the context of the [master] database:

if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')  
BEGIN   
 EXEC sp_addrole 'RSExecRole'  
END

That role definitely did not exist in [master]. In fact, there was not an "object" in [master] by that name:

SELECT o.name, o.type_desc
FROM master.sys.objects o
WHERE o.name = 'RSExecRole'

name        type_desc
----------  -----------------

(0 rows affected)

Upon further inspection, I realized it was an existing schema named RSExecRole that was causing the issue.

Dave Mason - SQL Server Schemas


If I had paid just a wee bit more attention to the error message I would have discovered this sooner:

CREATE SCHEMA failed due to previous errors.

For a quick turnaround, I deleted the RSExecRole schema from [master] and also from [msdb] (the Extended Events session tipped me off to this), re-ran "Change Database" in SSRS Configuration and finally got the results I was expecting. Happy migrating, everyone!

Dave Mason - Report Server Configuration Manager



Documenting SSIS Catalogs

Dave Mason - SQL Server - SSIS

In the last post, we looked at query options for documenting SSIS packages that are deployed via the legacy package deployment model. What about SSIS packages deployed to a catalog via the project deployment model? Is the package XML accessible so that we can query and shred it? I don't know with certainty, but I think it is not.

However, there are .NET Framework interfaces we can use to programatically obtain SSIS catalog metadata. So for this post, I'll soldier on without my dear friend T-SQL and proceed with PowerShell. Here's a look at the projects and packages I was working with as I developed and debugged the script code. It's a short list with just two projects, having one package each. The "DailyETLMain.dtsx" package is a sample from Microsoft's GitHub repository.

SSIS Catalog

As for the script itself, I waffled over whether to include it here or not. In the end, I decided to include it here (at the end of the post), but it's also on Github as part of a PowerShell repository. Note there are a number of assemblies referenced. I'm not sure where all of them originate from. I've got a lot of stuff installed on my laptop: SQL Server 2017 (including the Integration Services feature), Visual Studio, SSDT, and SSMS to name a few.

The script output is a table of names and descriptions including the catalog, folders, projects, packages, and package tasks (type, name, order, and description). The task order reflects the package precedent constraints, if any. There is no recursion for container objects--the script doesn't list tasks within a Sequence or a ForEachLoop container, for instance. (For documentation purposes, this seemed like overkill. So I opted against it.)

PowerShell Get-SsisCatalogMetada


Acknowledgments

I did want to pass along a thank you to StackOverflow user Jonathan Garvey. His answer to a question got me past a roadblock that I wasn't able to overcome. Thanks, Jonathan!

 <#
    MIT License
    Copyright (c) 2019 Dave Mason
    Permission is hereby granted, free of charge, to any person obtaining a copy
    of this software and associated documentation files (the "Software"), to deal
    in the Software without restriction, including without limitation the rights
    to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
    copies of the Software, and to permit persons to whom the Software is
    furnished to do so, subject to the following conditions:
    The above copyright notice and this permission notice shall be included in all
    copies or substantial portions of the Software.
    THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
    SOFTWARE.
#>

<#
    .SYNOPSIS
        Gets SSIS metadata.
    .DESCRIPTION
        Gets SSIS metadata including catalogs, folders, projects, packages, and tasks.
    .INPUTS
        Name of the SQL Server instance where the SSIS catalog database(s) reside(s).
    .OUTPUTS
        List of SSIS catalogs, folders, projects, packages, package task types, task names, task order, and task descriptions.
    .NOTES
        Version:        1.0
        Author:         Dave Mason
        Creation Date:  2019/09/27
#>

<#
    Assembly versions that worked on my laptop:
    GAC    Version        Location                                                                                                                                                                                        
    ---    -------        --------                                                                                                                                                                                        
    True   v4.0.30319     C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll                                                    
    True   v2.0.50727     C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll                               
    True   v2.0.50727     C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll                                                       
    True   v2.0.50727     C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll                                                                                     
    True   v4.0.30319     C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.IO.Compression\v4.0_4.0.0.0__b77a5c561934e089\System.IO.Compression.dll    
#>

# Possible issues: what if these assemblies aren't present? or are the wrong version?
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("System.IO.Compression")


<#
    Adds a row to the meta data table.
    (Params should be self-explanatory.)
#>
function Add-MetaDataTableRow([string]$catalog, [string]$folder, [string]$project, [string]$package, [string]$taskType, [string]$taskName, [string]$taskOrder, [string]$taskDescription)
{
    [System.Data.DataRow]$row = $SsisMetaData.NewRow()
    $row["Catalog"] = $catalog
    $row["Folder"] = $folder
    $row["Project"] = $project
    $row["Package"] = $package
    $row["Task Type"] = $taskType
    $row["Task Name"] = $taskName
    $row["Task Order"] = $taskOrder
    $row["Task Description"] = $taskDescription
            
    $SsisMetaData.Rows.Add($row)
}

function Get-SortedPrecedenceConstraints()
{
    [OutputType([System.Collections.Generic.List[Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint]])]
    Param (
        [parameter(Mandatory=$true)]
        [Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraints]
        $precedenceConstraints
    )
    # The PrecedenceConstraint objects in the collection are chained together. 
    # PrecedenceExecutable is the "previous" executable. 
    # ConstrainedExecutable is the "next" executable.

    $ret = [System.Collections.Generic.List[Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint]]::new()
    $ConstrainedExecutableIds = [System.Collections.Generic.List[string]]::new()

    #Create list of constrained executable Ids.
    for ([int]$i = 0; $i -lt $precedenceConstraints.Count; $i++)
    {
        [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$container = [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$precedenceConstraints[$i].ConstrainedExecutable
        $ConstrainedExecutableIds.Add($container.ID)
    }

    #Find the PrecedenceExecutable that doesn't have its ID in the string list.
    for ([int]$i = 0; $i -lt $precedenceConstraints.Count; $i++)
    {
        [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$container = [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$precedenceConstraints[$i].PrecedenceExecutable

        if ( -not $ConstrainedExecutableIds.Contains($container.ID) )
        {
            $ret.Add($precedenceConstraints[$i])
            break
        }
    }
    
    while ($ret.Count -lt $precedenceConstraints.Count)
    {
        [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$lastContainer = [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$ret[$ret.Count - 1].ConstrainedExecutable

        # Although the PrecedenceConstraints chain may be out of order,
        # don't assume the chain is unbroken. 
        [bool]$found = $false

        for ([int]$i = 0; $i -lt $precedenceConstraints.Count; $i++)
        {
            [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$container = [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$precedenceConstraints[$i].PrecedenceExecutable

            if ($container.ID -eq $lastContainer.ID)
            {
                $ret.Add($precedenceConstraints[$i])
                $found = $true
                break
            }
        }

        # TODO: throw an exception?
        if( -not $found )
        {
            break
        }
    }

    return $ret
}

function Get-TaskType()
{
    [OutputType([string])]
    Param (
        [parameter(Mandatory=$true)]
        [Microsoft.SqlServer.Dts.Runtime.EventsProvider]
        $container
    )

    [string]$ret = $container.GetType().Name

    if ([string]::Compare($ret, "TaskHost", $true) -eq 0)
    {
        $ret = ([Microsoft.SqlServer.Dts.Runtime.TaskHost]$container).InnerObject.GetType().ToString()
        $ret = $ret.Split('.')[$ret.Split('.').Length - 1]

        if([string]::Compare($ret, "__ComObject", $true) -eq 0)
        {
            #This seems to happen for Data Flow Tasks. 
            $ret = $container.GetType().Name
        }
    }

    return $ret
}


#region Create/reset DataTable, add columns.
[System.Data.DataTable]$SsisMetaData = [System.Data.DataTable]::new()
$SsisMetaData.Columns.Add("Catalog") | Out-Null
$SsisMetaData.Columns.Add("Folder") | Out-Null
$SsisMetaData.Columns.Add("Project") | Out-Null
$SsisMetaData.Columns.Add("Package") | Out-Null
$SsisMetaData.Columns.Add("Task Type") | Out-Null
$SsisMetaData.Columns.Add("Task Name") | Out-Null
$SsisMetaData.Columns.Add("Task Order") | Out-Null
$SsisMetaData.Columns.Add("Task Description") | Out-Null
#endregion

# Prompt user.
Write-host "Enter the SQL Server instance" -ForegroundColor Yellow 
[string]$SqlInstance = Read-Host " (where the SSIS catalog database(s) reside(s)) " 

if([string]::IsNullOrEmpty($SqlInstance))
{
    Write-host "Nothing entered." -ForegroundColor Magenta
    return
}

[System.Data.SqlClient.SqlConnection]$conn = [System.Data.SqlClient.SqlConnection]::new()
[System.Data.SqlClient.SqlConnectionStringBuilder]$csb = [System.Data.SqlClient.SqlConnectionStringBuilder]::new()
$csb["Initial Catalog"] = "tempdb"
#$csb["Data Source"] = ".\SQL2017"
$csb["Data Source"] = $SqlInstance
$csb["Integrated Security"] = $true
$conn.ConnectionString = $csb.ToString()
[Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]$intSvcs = [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]::new($conn)
           
foreach ($cat in $intSvcs.Catalogs)
{
    foreach ($folder in $cat.Folders)
    {
        foreach ($proj in $folder.Projects)
        {
            <#
                Code enhanced/adapted from Jonathan Garvey's StackOverflow answer:
                https://stackoverflow.com/questions/40439662/get-package-xml-from-ssis-catalog-with-powershell/#43368494
            #>
            [byte[]]$projectBytes = $intSvcs.Catalogs[$cat.Name].Folders[$folder.Name].Projects[$proj.Name].GetProjectBytes();
            [System.IO.Stream]$stream = [System.IO.MemoryStream]::new($projectBytes)
            [System.IO.Compression.ZipArchive]$za = [System.IO.Compression.ZipArchive]::new($stream)

            foreach ($pkgInfo in $proj.Packages)
            {
                foreach ($zipEntry in $za.Entries)
                {
                    if ($zipEntry.FullName -eq $pkgInfo.Name)
                    {
                        [Microsoft.SqlServer.Dts.Runtime.Package]$pkg = [Microsoft.SqlServer.Dts.Runtime.Package]::new()
                        [System.IO.StreamReader]$sr = [System.IO.StreamReader]::new($zipEntry.Open())
                        $pkg.LoadFromXML($sr.ReadToEnd(), $null)
        
                        [System.Collections.Generic.List[string]]$constrainedExeId = [System.Collections.Generic.List[string]]::new()
                        [System.Collections.Generic.List[Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint]]$precedenceConstraints = `
                            Get-SortedPrecedenceConstraints($pkg.PrecedenceConstraints);
                        [string]$TaskType = $null;

                        #region Iterate through the PrecedentConstraint objects.
                        # Find the executables that are part of precedent constraints.
                        # We'll want to output those first (in order).
                        if ($precedenceConstraints.Count -gt 0)
                        {
                            # Cast the Executable to an EventsProvider class so we can get 
                            # the .Name and .Description property values.
                            [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$container = `
                                [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$precedenceConstraints[0].PrecedenceExecutable
                            $TaskType = Get-TaskType($container)
                                            
                            Add-MetaDataTableRow $cat.Name $folder.Name $proj.Name $pkgInfo.Name $TaskType $container.Name "1" $container.Description
                            $constrainedExeId.Add($container.ID)

                            for ([int]$i = 0; $i -lt $precedenceConstraints.Count; $i++)
                            {
                                $container = [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$precedenceConstraints[$i].ConstrainedExecutable
                                $TaskType = Get-TaskType($container)
                                Add-MetaDataTableRow $cat.Name $folder.Name $proj.Name $pkgInfo.Name $TaskType $container.Name ($i+2).ToString() $container.Description
                                $constrainedExeId.Add($container.ID)
                            }
                        }
                        #endregion

                        #region Find remaining executables that are not part of a precedence constraint.
                        foreach ($exe in $pkg.Executables)
                        {
                            # Cast the Executable to an EventsProvider class so we can get 
                            # the .Name and .Description property values.
                            [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$container = [Microsoft.SqlServer.Dts.Runtime.EventsProvider]$exe

                            if ( -not $constrainedExeId.Contains($container.ID))
                            {
                                $TaskType = Get-TaskType($container)
                                Add-MetaDataTableRow $cat.Name $folder.Name $proj.Name $pkgInfo.Name $TaskType $container.Name "" $container.Description

                                
                            }
                        }
                        #endregion
                        break
                    }
                }
            }
        }
    }
}

$SsisMetaData | format-table | out-host
 


Documenting SSIS Packages

SQL Server Integration Services

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.

Visual Studio SSIS Package

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.

Visual Studio - SSIS Sequence Container


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.

Shredded SSIS package XML

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!

Visual Studio - SSIS Sequence properties


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...


Documenting Maintenance Plans

SQL Server Maintenance Plan Wizard

I'm not a regular user of Maintenance Plans for SQL Server, but I run into them from time to time. I had a task to document all of the SQL Agent jobs, which for a number of environments, included some Maintenance Plans. This became a more time consuming task than I had anticipated!

I had known beforehand that Maintenance Plans were SSIS packages under the covers. So I started with a query on msdb.dbo.sysssispackages. I also knew that SSIS packages are essentially XML data. This query returns one row for each Maintenance Plan:

SELECT CAST(CAST([packagedata] AS VARBINARY(MAX)) AS XML) AS [Maintenance_Plan_Xml], 
 p.name Package_Name, p.description Package_Description, 
 p.createdate Create_Date, p.isencrypted Is_Encrypted
FROM msdb.dbo.sysssispackages p
WHERE p.packagetype = 6 --Maint Plans

If we look at the raw XML data, it's huge. It's too unwieldy to embed within this post, but you can check it out in its entirety if you like. My first thought was to try to shred the XML, but when I saw how vast and complex it was, I was less than intrepid. Still, I wanted to try. Using the SSMS GUI helped. Here's what the maintenance plan looks like:

SQL Server Maintenance Plan

Viewing the Maintenance Package as an SSIS package in Visual Studio also helped make sense out of the XML data. To create the SSIS package, I copied the XML output from the query into a .dtsx file. Here's what it looks like:

SQL Server SSIS Package

In both images, we can see the three subplans for the Maintenance Plan. As an SSIS package in Visual Studio, we can more easily see all of the tasks for each subplan. Visual Studio also tells us that each subplan is a Sequence Container. Lastly, I noted each subplan executes a Reporting Task upon completion, which I believe is for logging the outcome of each subplan. I decided to ignore these for my documentation purposes.


XML Shredding

Below is the query to shred the XML of each maintenance plan we saw from the first query (above). Without getting too deep into the XML query syntax, note the CROSS APPLY uses the .nodes() method to find Sequence objects (the subplans) of the Maintenance Plan SSIS package (DTS:Executable[@DTS:ExecutableType="STOCK:SEQUENCE"]).

WITH XMLNAMESPACES (
 'www.microsoft.com/SqlServer/Dts' AS DTS,
 'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
SELECT p.Package_Name,
 tasks.value('(../../@DTS:ObjectName)', 'VARCHAR(4000)') AS SubPlanName,
 tasks.value('(../../@DTS:Description)', 'VARCHAR(4000)') AS SubPlanDescription,

 tasks.value('(@DTS:ObjectName)', 'VARCHAR(4000)') AS TaskName,
 tasks.value('(@DTS:Description)', 'VARCHAR(4000)') AS TaskDescription
FROM
(
 SELECT CAST(CAST([packagedata] AS VARBINARY(MAX)) AS XML) AS [Maintenance_Plan_Xml], 
  p.name Package_Name, p.description Package_Description, 
  p.createdate Create_Date, p.isencrypted Is_Encrypted
 FROM msdb.dbo.sysssispackages p
 WHERE p.packagetype = 6 --Maint Plans
) p
CROSS APPLY p.Maintenance_Plan_Xml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable[@DTS:ExecutableType="STOCK:SEQUENCE"]/DTS:Executables/DTS:Executable') AS t(tasks) 

The output is a tabular result showing the Maintenance Plan, its subplan Names and Descriptions, and the task Names and Descriptions of each subplan.

SSMS Query Results

As developers, we have full control over all of the meta data. The subplan names and descriptions can be entered directly from SSMS: double-click a subplan to open its Properties window. Here you can change the subplan Name and Description (among others).

SQL Server Maintenance Plan - Subplan Properties

To change the properties of a subplan task, right-click the task and choose Properties from the pop-up menu. From there, the task Name and Description properties can be set (plus many, many others).

SQL Server Maintenance Plan - Subplan Task Properties


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 2017, 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,
 tasks.value('(../DTS:Property[@DTS:Name="ObjectName"][1])', 'VARCHAR(4000)') AS SubPlanName,
 tasks.value('(../DTS:Property[@DTS:Name="Description"][1])', 'VARCHAR(4000)') AS SubPlanDescription,

 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 CAST(CAST([packagedata] AS VARBINARY(MAX)) AS XML) AS [Maintenance_Plan_Xml], 
  p.name Package_Name, p.description Package_Description, 
  p.createdate Create_Date, p.isencrypted Is_Encrypted
 FROM msdb.dbo.sysssispackages p
 WHERE p.packagetype = 6
) p
CROSS APPLY p.Maintenance_Plan_Xml.nodes('/DTS:Executable/DTS:Executable[@DTS:ExecutableType="STOCK:SEQUENCE"]/DTS:Executable') AS t(tasks) 

Maintenance Plans might make some of us wince, but if you encounter them, now they should be easy to document. Happy shredding!