PowerShell SSIS deployment and maintenance

More than a year ago, I wrote my little post on deploying an SSISManifest file via a PowerShell script which in turn repeatedly invoked dtutil. That was fine and dandy but I've grown since then, taken a new job and discovered that they didn't install all of SQL Server when they created developer machines. We have Visual Studio Team Foundation Server Database Edition aka Data Dude and we have BIDS and the SQL Server client tools installed but there is no joy in Mudville when I attempt to use any of the tools outside of Visual Studio (dtutil, dtexec, dtsinstall).

Invariably, whatever command I attempt to execute will result in an error message stating I don't have integration services installed. dtutil.exe -> "The SQL Server SSIS Package Utilities require Integration Services to be installed by one of these editions of SQL Server 2008 R2: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services." dtsinstall.exe -> "ERROR: The Package Installation Wizard requires Integration Services to be installed by one of these editions..." dtexec.exe -> "To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher." I'm sure from a licensing perspective, this post probably violates the spirit but I'm going to sidestep that for now and assume you, the awesome reader (Hi Mom!), is interested in this solution so that you can have more power over your SSIS installations.

I've learned quite a bit of PowerShell syntax over the past 2 months in my new job. It's nothing I had to do, wasn't even a bullet point on their required skills but I've been needing to glue some things together and PowerShell seemed the perfect tool. As a developer, I really see PowerShell as a scriptable .NET language. It's decent for prototyping and shouldn't raise hackles like "here's an exe, just run it on the server, it's fine"

PowerShell SSIS deployment and maintenance

This script builds on the concepts of my earlier script but ditches wrapping the dtutil method in lieu of directly using the object model. Plus, it feels like the more correct solution. Save this script as something like C:\src\DeployManifest.ps1

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null

# This method will create a folder on SQL Server for SSIS package deployment
Function Create-FolderOnSqlServer
{
    param
    (
        [string]$Server,
        [string]$Folder
    )
    
    $folderDelimter = "\"
    
    try
    {
        # if they supply multiple-level of folders, might need to create whole structure
        # while recursion would be a more elegant solution, my mind is not there
        if ($Folder -ne $folderDelimter)
        {
            $folderList = $Folder.Split($folderDelimter)
        }
        else
        {
            $folderList = [array]$Folder
        }
        
        $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
        $parentFolder = "\"

        foreach($subFolder in $folderList)
        {
            # test for existing folders
            Write-Debug ([System.string]::Format("parentFolder:{0} folderDelimter:{1} subFolder:{2}", $parentFolder, $folderDelimter, $subFolder))
            $testFolder = $parentFolder + $folderDelimter + $subFolder
            if (!$app.FolderExistsOnSqlServer($testFolder, $Server, $null, $null))
            {
                Write-Debug ([System.string]::Format("Folder {0} does not exist on server, creating", $testFolder))
                $app.CreateFolderOnSqlServer($parentFolder, $subFolder, $Server, $null, $null);
                Write-Host ([System.string]::Format("Created folder {0} on server {1}", $testFolder, $Server))
            }
            else
            {
                Write-Debug "Folder exists on server, doing nothing"
            }
            
            $parentFolder += $folderDelimter + $subFolder
        }
    }
    catch 
    {
        Write-Error ([string]::Format("Failed to create folder {0} on server {1}", $folder, $server))
        Write-Error $_ | fl * -Force
    }
}

# Deploy a package to a folder
Function Copy-Package
{
    param
    (
        [string]$Server,
        [string]$PathToDtsx,
        [string]$ProjectFolder = "\\"
    )
    
    $events = $null
    $userName = $null
    $password = $null
    
    Write-Debug "--------------------------Copy-Package----------------------"
    Write-Debug ([string]::Format("File: {0}", $PathToDtsx))
    Write-Debug ([string]::Format("Server: {0}", $Server))
    Write-Debug ([string]::Format("Folder: {0}", $ProjectFolder))

    if ($ProjectFolder -ne "\\")
    {
        Create-FolderOnSqlServer $Server $ProjectFolder
    }

    Write-Debug ([string]::Format("Attempting to deploy {0} to server {1}", $PathToDtsx, $Server))
    
    try
    {
        $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
        $package = $app.LoadPackage($PathToDtsx, $null)
        
        $destinationName = "\\"
        if ($ProjectFolder -ne "\\")
        {
            $destinationName = $destinationName + $ProjectFolder + "__"
        }

        # \\folder\\packageName
        $destinationName = $destinationName + $package.Name
        Write-Debug ([string]::Format("Saving as {0}", $destinationName))
        $app.SaveToSqlServerAs($package, $events, $destinationName, $Server, $userName, $password);
        Write-Host ([string]::Format("Deployed {0}", $destinationName))
    }
    catch 
    {
        Write-Error ([string]::Format("Failed to deploy {0} to server {1}", $PathToDtsx, $Server))
        Write-Error $_ | fl * -Force
    }
}

# This method cracks open an SSIS manifest file and deploys all packages to
# the indicated server
Function Deploy-Manifest
{
    param
    (
        [string]$Server,
        [string]$ManifestFile,
        [string]$ProjectFolder = "\\"
    )
    # This deploy script assumes all packages are in the same folder as the 
    # manifest

    $baseFolder = [System.IO.Path]::GetDirectoryName($ManifestFile)

    [xml] $list = Get-Content $ManifestFile

    Write-Debug "--------------------------Deploy-Manifest----------------------"
    Write-Debug ([string]::Format("    Server {0}", $Server))
    Write-Debug ([string]::Format("    ProjectFolder {0}", $ProjectFolder))
    foreach($package in $list.DTSDeploymentManifest.Package)
    {
        $basePackage = [System.IO.Path]::GetFileNameWithoutExtension($package)
        
        # This might need to be a relative path
        $fullyQualifiedPackage = [System.IO.Path]::Combine($baseFolder, $package)
        Write-Debug ([string]::Format("    Package {0}", $fullyQualifiedPackage))
    
        Copy-Package $Server $fullyQualifiedPackage $ProjectFolder
    }
}

# this script is reponsible for enumerating subfolder
# for each manifest it finds, it will invoke the deploy method on it
Function Walk-SubFolder
{
    param
    (
        [string]$RootFolder,
        [string]$DeployServer
    )
    
    $searchPattern = "*.ssisdeploymentmanifest"
    
    foreach($manifestFile in [System.IO.Directory]::GetFiles($RootFolder, $searchPattern, [System.IO.SearchOption]::AllDirectories))
    {
        [xml] $list = Get-Content $manifestFile
        # hard coding the root folder DBServer
        $deployFolder = ([string]::Format("Default\{0}", $list.DTSDeploymentManifest.GeneratedFromProjectName))
        Write-Host ([string]::Format("Deploying manifest file {0}", $manifestFile))
        Deploy-Manifest $DeployServer $manifestFile $deployFolder
    }
}

What I like about this approach is that it let's me get my job done, it allows for unattended installs of SSIS and it handles deploys to subfolders. It still has warts though, I think it's fairly obvious I've not spent a great deal of time polishing it. I'm still learning PowerShell so I was trying to escape backslashes early on but they don't seem to matter, or at least the assembly's methods don't seem to mind doubled and singled backslashes. The error handling is something I've scrounged from the interblags so I don't know if it's the best approach or not.

Usage

The purpose of DeployManifest.PS1 is to crawl all the subfolders from a given root, looking for anything that has an .ssisDeploymentManifest file. For every one of those it finds, it will rip the XML (manifest) apart and extract the project name and the list of packages. It will create a nested folder on the server (Default\ProjectName) and then deploy all the packages Assumptions:

• Deployments will go to Default\ProjectName
• The ssisDeploymentManifest will be in the same folder as the dtsx packages it lists
• You have run this command in PowerShell: Set-ExecutionPolicy Remote-Signed

C:\Src
      \ModuleA\Solution1\ProjectFoo\
                                    ProjectFoo.ssisDeploymentManifest
                                    Package1.dtsx
                                    Package2.dtsx
      \ModuleB\Solution2\Sales\
                               Sales.ssisDeploymentManifest
                               SalesComputeCommission.dtsx
                               SalesGenerateManagerSummaries.dtsx
              \Staging\Staging\
                               Staging.ssisDeploymentManifest
                               StagingLoadFiles.dtsx

Given a structure like the above, we could chose to deploy everything or just a specific project based on the starting folder (Src, ModuleA, Staging). Create a second PowerShell script, this one will actually use the library we built above and saved to C:\Src\DeployManifest.ps1 This script is handy in that it can be dropped at any location in that Src tree and it will deploy everything below it. Simply save the following script as DeployPackages.ps1 and try invoking it from different locations. If it's in \Src, it should find 3 manifests, create 4 folders (Default + 3 project subfolders) and serialize 5 packages into msdb (msdb.dbo.sysssispackages or msdb.dbo.sysdtspackages90 depending on your SQL Server version)

# This script attempts to deploy everything below this folder that has an SSIS manifest file

# This is the "dot include" mechanism for PowerShell which is 
# the PowerShell way of re-using an existing script
. "C:\Src\DeployManifest.ps1"

$deployServer = "localhost\DEV2008R2"
$baseFolder = ".\"
Walk-SubFolder $baseFolder $deployServer

What's left? I have the basics of package delete and a recursive folder delete but I'm reworking it to make it more PowerShell-onic (function names and parameters don't meet conventions etc)