PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

I have made no progress since 2008 on the SSIS Uploader. I still think it has merit and I hope to finish it some day. Today is not that day.

Background

At work, we are in the midst of rolling out SQL Server 2008. No R2, just vanilla SQL Server 2008 (named instance) in a side-by-side installation with the 2005 default instance. We are not in production yet but today marked the first issue we had encounted with it. We deploy SSIS packages via the dtsinstaller. It consumes a manifest file (XML) and deploys all of the specified packages into the destination location: file system or into SQL Server (msdb) the installer handles them both. We encountered the following error message today "Unable to find an entry point named 'DTSSKUIsWKGorUnder' in DLL 'dts'. (DTSInstall)." When Google turns up one hit and it's to a Connect article, that's never an encouraging sign. Labouring under the delusion that dtutil will work better than the fancy dtsinstaller, the thought struck me that I could get the benefits of a command line deploy and still incorporate the deployment manifest. For extra points, it seemed like a good reason to use PowerShell to glue it all together.

PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

The following script is my first pass at enumerating the elements of a SSISDeploymentManifest file and using dtutil to install those packages to a 2008 named SQL Server instance. Ain't that a mouthful?

# 2010-04-30
# Bill Fellows
# This PowerShell script is designed to shred an SSISDeploymentManifest
# and deploy file based SSIS packages into a SQL Server instance.
#
# Being the extreme novice with PowerShell, everything is hardcoded and 
# it assumes you have dtutil installed on the same drive as this script.
# Possible enhancements would be to look in the registry for location, 
# accept parameters, encapsulate code into a cmdlet, others as they come up

$format = """\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe"" /file ""{0}"" /DestServer {1} /COPY SQL;""{2}"""

# This deploy script assumes all packages are in the same folder as the 
# manifest
$fileName = "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\SSISHackAndSlash.SSISDeploymentManifest"

# The destination server, this should be a parameter
$destinationServer = "localhost\SQL2008"

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

[xml] $list = Get-Content $fileName

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)
    
    $cmd = [string]::Format($format, $fullyQualifiedPackage, $destinationServer, $basePackage)
 
    cmd /c $cmd
}

The script is simple enough. Given a manifest file in the form of

<?xml version="1.0"?> 
<DTSDeploymentManifest GeneratedBy="HOME\administrator" GeneratedFromProjectName="SSISHackAndSlash" GeneratedDate="2010-04-30T21:19:08.6336677-05:00" AllowConfigurationChanges="true">
   <Package>Package.dtsx</Package>
   <Package>Package2.dtsx</Package>
   <Package>Package7.dtsx</Package>
</DTSDeploymentManifest>

it will simply run the following commands

"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package"
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package2.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package2"
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package7.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package7"

What I like about this solution

• Scriptable---our deploys no longer require UI interaction so RDP rights no longer required
• Uses manifest file---Our devs are trained to look at the manifest as the definitive source for SSIS deployments
• It uses PowerShell

The big drawback in my mind, is that there is something buggered up with our servers. Ultimately, this is a workaround until we resolve that issue but if it becomes our defacto deploy method, I think we'll be better off.