Deploying Maintenance Plans with PowerShell or ASP.NET

I feel that my job as a DBA is very dynamic and usually keeps me busy with new things... except for deploying maintenance plans.  I hate making maintenance plans.  Luckily, I at least have a template that I can use to keep everything uniform, but it requires me opening up the plan in Visual Studio every time I want to deploy it to a new server.  I'm sure there are many different solutions for this problem, but I came up with a very elegant method that I was able to integrate into my intranet server inventory system.

The first thing I did was make a copy of my existing maintenance plan templates.  If you don't have a template, you should be able to export an existing maintenance plan out of one of your existing servers.  It can be imported into Business Intelligence Studio from the server as well.

Once I had my templates ready, I edited the Local Server Connection and the backup and cleanup task paths.  Instead of setting a real server name or backup path, I used placeholders.  I went with [SERVERNAME] for the Local server connection and [BACKUPPATH] in the backup and cleanup tasks.

What I didn't realize before was that the DTSX files that I had been editing were pure XML files in disguise.  So really all my PowerShell script needs to do is to read in the file, replace text and write it back out to a temporary DTSX file.  Once that was written, I could then use the required assembly and methods to read it in as a package and deploy it to my server.

If you are going to be deploying SQL 2008 packages, you need the 10.0.0.0 version of Microsoft.SqlServer.ManagedDTS assembly.  Version 10 will handle both 2005 and 2008 SSIS packages.  If you only have version 9.0.x.x installed, you will not be able to deploy 2008 packages.

I got the idea and information on the assembly I needed from MidnightDBA.com.  Here's some of the PowerShell code:

Since I need to perform the same steps over and over, I created a function to deploy the package and then call the function for each maintenance plan I want to deploy.

#Load up the assembly [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") | Out-Null;

#Create function that can be called function createPackage {param ($filename, $foldername, $sqlversion)

# Read in my maintenance plan template and replace the place holders $packageContents = [IO.File]::ReadAllText(".\$sqlVersion\$filename") $packageContents = $packageContents.Replace("[SERVERNAME]","$ServerName") $packageContents = $packageContents.Replace("[BACKUPPATH]","$backupPath\$foldername")

# Write the new package.  MUST use UTF8 encoding or it will break. Set-Content -Encoding utf8 ".\out.dtsx" $packageContents

# Load the new package $Loadedpkg = $app.LoadPackage($pkg, $null, $null) $Pkgname = $Loadedpkg.Name

#Save the maintenance plan to the server $app.SaveToSQLServerAs($Loadedpkg, $null, "\\Maintenance Plans\$Pkgname", $ServerName, $null, $null) Write-Host "$Pkgname deployed to $ServerName" }

#Now for the main program code... $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application; $Loadedpkg = New-Object Microsoft.SqlServer.Dts.Runtime.Package; $DtsxPath = ".\" $pkg = "$DtsxPath\out.dtsx"

# If you need to deploy both versions of SQL like me, # you can have sub-folders with templates for each version if you like. $SQLVersion = Read-Host "2005 or 2008?"

if($SQLVersion -ne "2005" -AND $SQLVersion -ne "2008") { Write-Host "$SQLVersion is not a valid option." exit }

#Get the server name to deploy to... $ServerName = Read-Host "Enter name of SQL Server" $ServerName = $ServerName.ToUpper()

# pass the name of the template file, the subfolder in your backup path, and the version of SQL. # i typically have a User folder for user database backups and a System folder for System database backups. createPackage "HistoryCleanupTemplate.dtsx" "" "$SQLVersion" createPackage "SystemDBMaintTemplate.dtsx" "System" "$SQLVersion" createPackage "UserDBMaintTemplate.dtsx" "User" "$SQLVersion" createPackage "UserTLogMaintTemplate.dtsx" "User" "$SQLVersion"

Voila - your plans should be pushed out to the server.

This code can easily be converted to ASP.NET, which I decided was the better solution.  This allows me to deploy maintenance plans directly from a webpage, instead of having to use PowerShell on a computer that may not have the proper assemblies installed.  This does require installing Integration Services onto your web server, however.

I have simplified the code to the bare essentials.  I'm not doing error checking or anything fancy.  Here are the functions that would go in the page code.  You are responsible for figuring out how you want the whole thing to work, but I have a basic web page created that allows me to enter the name of my SQL Server (text box), the backup path (text box or dropdown) and the version of SQL Server (dropdown) to determine which templates to use.  Finally, I have a label at the bottom of the page that I can use to provide the user with information.  I should also mention that your ASP.NET application pool account will need to be able to write the temporary DTSX file out to a folder location.

Protected Sub deployBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles deployBtn.Click resultsLabel.Text = "" Dim SQLVersion As String = versionDDL.SelectedValue 'You will retrieve this value from a textbox or dropdown if it changes from server to server. Dim backupPath As String = ""

'If you wish, you can create the paths with the script to avoid having to do it manually. IO.Directory.CreateDirectory(backupPath & "\User") IO.Directory.CreateDirectory(backupPath & "\System")

'Log your work to a simple label so that the user has confirmation of their actions resultsLabel.Text &= "Backup directories created for " & ServerName & "
" resultsLabel.Text &= createPackage(ServerName, backupPath, "HistoryCleanupTemplate.dtsx", "", SQLVersion) resultsLabel.Text &= createPackage(ServerName, backupPath, "SystemDBMaintTemplate.dtsx", "System", SQLVersion) resultsLabel.Text &= createPackage(ServerName, backupPath, "UserDBMaintTemplate.dtsx", "User", SQLVersion) resultsLabel.Text &= createPackage(ServerName, backupPath, "UserTLogMaintTemplate.dtsx", "User", SQLVersion)

IO.File.Delete(Server.MapPath(".\") & "out.dtsx") End Sub

Function createPackage(ByVal serverName As String, ByVal backupPath As String, ByVal filename As String, ByVal folderName As String, ByVal sqlVersion As String) As String ' Read in your package as text out of either a 2005 or 2008 folder containing the templates. ' I assume the folders are in the same location as your asp.net files Dim packageContents As String = IO.File.ReadAllText(Server.MapPath(".\" & sqlVersion) & "\" & filename)

' Replace your placeholders with the Server and backup paths packageContents = packageContents.Replace("[SERVERNAME]", serverName) packageContents = packageContents.Replace("[BACKUPPATH]", backupPath & "\" & folderName)

' Set the path to write your temporary DTSX file. Dim DtsxPath As String = Server.MapPath(".\") Dim pkg As String = DtsxPath & "out.dtsx"

' Write the new package to the disk with UTF8 encoding. IO.File.WriteAllText(pkg, packageContents, System.Text.Encoding.UTF8)

' Create your SSIS objects Dim app As New Microsoft.SqlServer.Dts.Runtime.Application Dim Loadedpkg = New Microsoft.SqlServer.Dts.Runtime.Package Dim pkgName As String

' Load your new temporary package from disk. Loadedpkg = app.LoadPackage(pkg, Nothing, True) pkgName = Loadedpkg.Name

' Deploy to SQL Server app.SaveToSqlServerAs(Loadedpkg, Nothing, "\\Maintenance Plans\" & pkgName, serverName, Nothing, Nothing)

' I choose to return the results of the function as a string with a success message createPackage = pkgName & " deployed to " & serverName & " successfully.
"

End Function

That just about covers it. As I said previously, I added this functionality into an intranet inventory system I created to manage all of my SQL Servers. When I add a new server to my inventory, I click a link to deploy maintenance plans and move on about my day. I still have to go in just to make sure my proper databases are selected, but for the most part it takes just a few minutes compared to the 15 to 20 minutes I was spending before.