http://www.sqlservercentral.com/blogs/sqlballs/2013/01/21/how-do-you-query-maintenance-plan-package-metadata/ Printed 2014/03/08 01:17PM
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.
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
) 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.”
you say, “You figured this out on your own, and answered the question?”
Excellent question Dear Reader! No, I didn’t.
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.
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.
I first cast the image to a varchar(max), and lo and behold it was actually in XML format! So I wrote this query.
,CAST(CAST(packagedata as varbinary(max)) as xml) AS PackageSource 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) , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package FROM [msdb].[dbo].[sysssispackages] WHERE name = 'MaintenancePlan' ,c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)', 'NVARCHAR(MAX)') CROSS APPLY package.nodes('//DTS:ObjectData') t(c) WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1
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!