Download all SSIS Packages with Powershell

Prior to an implementation, I have been in the habit of making a copy of all my ssis packages.  While they do exist in source control, it is far easier to recover from a local copy.  If you have ever tried edit a prior version of a package from TFS, you'll know what I mean.  (TFS likes to download the latest copy on check out.)  This is like a backup for SSIS packages.

Below is a script that utilizes an extension for powershell that can be found here:  http://sqlpsx.codeplex.com/

This could be accomplished by calling DTUTIL, but this syntax is much nicer.

Import-module SSIS

$TargetFolder = "c:\temp\ssis_Packages"
$Server = "localhost

#Get packages from ETL Folder
$Packages = Get-ISItem -topLevelFolder "\" -ServerName $Server -path "\ETL" -recurse

#Delete files from release folder
if(Test-Path "$TargetFolder")
{
    Remove-Item "$TargetFolder\*"
}
else
{
    New-Item -path "$TargetFolder" -type Directory
}

#Add Packages to release folder

foreach($Pack in $Packages)
{
    $packpath = $pack.literalpath
    $packDest = $TargetFolder + "\" + $pack.name + ".dtsx"

    Copy-ISItemSQLToFile -path $packPath -topLevelFolder "msdb" -Servername $Server -destination $packDest -protectionlevel DontSaveSensitive -force
}

Invoke-item $TargetFolder

Mark Wojciechowicz
2013-02-22

Labels: ,