Export all SSIS packages from msdb using Powershell
- by jamiet
Have you ever wanted to dump all the SSIS packages stored in msdb out to files? Of course you have, who wouldn’t? Right? Well, at least one person does because this was the subject of a thread (save all ssis packages to file) on the SSIS forum earlier today.
Some of you may have already figured out a way of doing this but for those that haven’t here is a nifty little script that will do it for you and it uses our favourite jack-of-all tools … Powershell!!
Imagine I have the following package folder structure on my Integration Services server (i.e. in [msdb]):
There are two packages in there called “20110111 Chaining Expression components” & “Package”, I want to export those two packages into a folder structure that mirrors that in [msdb]. Here is the Powershell script that will do that:Param($SQLInstance = "localhost")
#####Add all the SQL goodies (including Invoke-Sqlcmd)#####
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls
$Packages = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "WITH cte AS (
SELECT cast(foldername as varchar(max)) as folderpath, folderid
FROM msdb..sysssispackagefolders
WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
UNION ALL
SELECT cast(c.folderpath + '\' + f.foldername as varchar(max)), f.folderid
FROM msdb..sysssispackagefolders f
INNER JOIN cte c ON c.folderid = f.parentfolderid
)
SELECT c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
FROM cte c
INNER JOIN msdb..sysssispackages p ON c.folderid = p.folderid
WHERE c.folderpath NOT LIKE 'Data Collector%'"
Foreach ($pkg in $Packages)
{
$pkgName = $Pkg.name
$folderPath = $Pkg.folderpath
$fullfolderPath = "c:\temp\$folderPath\"
if(!(test-path -path $fullfolderPath))
{
mkdir $fullfolderPath | Out-Null
}
$pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx"
}
To run it simply change the “localhost” parameter of the server you want to connect to either by editing the script or passing it in when the script is executed. It will create the folder structure in C:\Temp (which you can also easily change if you so wish – just edit the script accordingly). Here’s the folder structure that it created for me:
Notice how it is a mirror of the folder structure in [msdb].
Hope this is useful!
@Jamiet
UPDATE: THis post prompted Chad Miller to write a post describing his Powershell add-in that utilises a SSIS API to do exporting of packages. Go take a read here: http://sev17.com/2011/02/importing-and-exporting-ssis-packages-using-powershell/
