PowerShell to deploy SSIS packages (package deployment model)

For a long time, I’ve been deploying SSIS packages to different environments manually by copying .dtsx files to target servers. I wouldn’t say this method is wrong, but I must say it’s time-consuming, error-prone, and have perks of any other manual processes. I know this has got to change. So I decided to learn and adapt a better, automated process.

This post is the first step of my learnings. I’ll continue to publish more posts in the future as I experiment other things that stabilize the automation of SSIS deployments.

Here is the PowerShell script to deploy SSIS 2008 and SSIS 2012 (Package Deployment Model) packages. It deploys packages to file system. It does two things:

  • Deletes all existing packages from the specified project folder
  • Adds packages from source to the target folder
 $DestinationPath = "C:\ETLApplications\psdeploy\"
$SourcePath = "C:\Source\SSIS\psdeploy\"
Write-Host "Deleting packages..."
Get-ChildItem $DestinationPath -filter "*.dtsx" | ForEach-Object{ Invoke-Expression "DTUTIL /FILE $DestinationPath$($_.Name) /DELETE" }
Write-Host "Adding packages..."
Get-ChildItem $SourcePath -filter "*.dtsx" | ForEach-Object { Invoke-Expression "DTUTIL /FILE $SourcePath$($_.Name) /COPY 'FILE;$DestinationPath$($_.Name)' "}
Write-Host "Deployment done. tada"

It’s only two lines of code, excluding variable declarations and Write-Host cmdlets to write messages. First, I declare two variables $DestinationPath and $SourcePath to assign destination and source folder paths. As you can imagine, I’ll be able to use the same script for any project in the future simply by changing these two variable values. Write-Host — similar to Print command in T-SQL — writes the message in double quotes to the PowerShell console.

In line 4, I delete the existing packages. For that, I use Get-ChildItem cmdlet to get information about the files in destination path. I also add -filter parameter to retrieve only .dtsx files. I then use pipe (|) to pass the results to a ForEach-Object loop. Invoke-Expression is used to call dtutil.exe for each row in the result set. I use /File parameter to set the target file that is to be deleted, by concatinating variable $DestinationPath and file name object from result set $_.Name. Last paratemeter /DELETE is to ask dtutil to delete the file.

In line 6, I add (deploy) packages to the target folder. It’s similar to the code in line 4 except for the dtutil syntax to copy files instead of deleting them. It’s important to observe the right most portion of line 6. You’ll notice that value for /COPY parameter is enclosed in single quotes (‘ ‘). That’s because of the semicolon (;) in the syntax after FILE. Similar to T-SQL, semicolon terminates a PowerShell (ps) statement. To make ps escape and ignore the semicolon here, I enclosed FILE;$DestinationPath$($_.Name) in single quotes as ”FILE;$DestinationPath$($_.Name)’.

I love automation, it’s simply amazing! Once you automate, you’ll never go back to the old way of doing things manually. I’m going to write a lot more posts about automating deployments and also automatically creating SSIS packages here on this blog. Subscribe to my RSS Feed in your favorite reader. Subscribe via email by entering your address in the top right corner. Follow me on twitter. Find out how I automate several database and BI functions!

One thought on “PowerShell to deploy SSIS packages (package deployment model)

Leave a Reply

Your email address will not be published. Required fields are marked *