How Do You Query Maintenance Plan Package Metadata?

 Hello Dear Reader!  Since I’ve joined Pragmatic Works I’ve learned a lot, seen a lot, and assisted in interviewing a lot.  One of the things I’m often asked in the interviewing process is, “What is the best thing about working for Pragmatic Works?”   My answer is always the same, the People. 

We have some really brilliant people, and when you get one of us to work with you get a whole crew backing them up. 

  I have folks like MVP Jorge Segarra (@SQLChicken | Blog), Kathi Kellenberger (@AuntKathi|Blog), and MVP Jody Roberts(@Jody_WP|Blog)  and many others that are a phone call away.

 We have a DL, Distribution List, for out BI Geniuses, the DBA crew, and for many other incredible areas our business covers.  So today my buddy and fellow DBA Chad Churchwell (@ChadChurchwell | Blog) tossed out a question to the DBA DL, “Has anyone ever found a way to query the maintenance plans in SQL Server… they are nothing more than SSIS packages, but I am trying to go is find a way to programmatically get the backup location of the backup database task within the maintenance plans.”

“So Balls”, you say, “You figured this out on your own, and answered the question?”

Excellent question Dear Reader!  No, I didn’t. 

The answer was a great collaboration, I got a little bit, MCM and MVP Jason Strate (@StrateSQL | Blog) ran further with it, and Chad came back and delivered a great script.   To cut to the chase go read Chad’s blog with the final script here.

HOW’S IT MADE!?

http://ratemydesktop.org/pm/78H0/yay_science.html

When you make a Maintenance Plan in SQL Server it saves it internally as an SSIS package.  Instead of browsing through the tables in the MSDB database I opened up profiler and clicked through the GUI.  I created a folder on my C:\ called FindMe and another named FindMe2.

I then let the trace run as I created an Maintenance plan to backup AdventureWorks2012.  I saved it.  Closed it and then reopened and modified the folder path.  

I started looking through the profiler trace for the folder text.  I figured it would be passed through as a parameter.  It wasn’t.  Instead I found a call to msdb.dbo.sp_ssis_putpackage.  

Parameter @P6 for sp_ssis_putpackage was a rather large hexadecimal blob.  Using sp_helptext I ran the following script.

sp_helptext 'sp_ssis_putpackage'

The output showed me that @P6 was part of @packagedata and it was saved as column packagedata, an image data type, in table msdb.dbo.sysssispackages.  I queried the column I translated FindMe2 to hex, 46696e644d6532. I searched the package for the hex string and found it!  So I knew that I had arrived at the right place.

*(If you want to check my hex take each number as a 2 number pair, ie 46 translate to binary 01000110 then translate the binary to ascii = F 69=i 6e=n 64=d 4d=M 65=e 32=2, FindMe2).

I first cast the  image to a varchar(max), and lo and behold it was actually in XML format!  So I wrote this query.

SELECT name

      ,CAST(CAST(packagedata as varbinary(max)) as xml) AS PackageSource

      ,packagetype

       ,id

      ,description

      ,createdate

  FROM msdb.dbo.sysssispackages

This gave me an XML document I could see that contained all of the metadata.  I passed this around to the collective brain trust and Jason Strate immediately responded with an XQUERY to get the data Chad needed.

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS

, 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)

,ssis AS (

    SELECT name

        , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package

    FROM [msdb].[dbo].[sysssispackages]

    WHERE name = 'MaintenancePlan'

   )

SELECT s.name

    ,c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'NVARCHAR(MAX)')

FROM ssis s

    CROSS APPLY package.nodes('//DTS:ObjectData') t(c)

WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1

Chad then took it to the next level by writing the script to be compatible in SQL 2005 and SQL 2008 and up.  To see the final version though go check outhis blog on it!

WRAP IT UP

Every place that I’ve worked the people made the difference.  Having a solid team to bounce ideas and questions off of not only helps you grow, but it helps them as well.

So a very cool day collaborating with my fellow DBA’s, and it netted a script and some background info I thought you might be able to use.  

It was a lot of fun for me to learn, and I got a new script to toss in my tool box.

Until next time, Thanks for stopping by, and make mine SQL!

Thanks,

Brad