THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Export all SSIS packages from msdb using Powershell

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

$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,,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 = $
    $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!


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:

Published Wednesday, February 02, 2011 8:45 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Chad Miller said:


Rather than directly hitting system tables, there is an API for working with SSIS:  

As part of the CodePlex project SQL Server PowerShell Extensions (SQLPSX), I created PowerShell functions which make use of the API to handle import and export and other SSIS management tasks. After seeing this post, I blogged about how you would do the same thing using SQLPSX:

February 3, 2011 7:16 AM

Frank Hell said:

For SQL Server 2005 you have to use this because 'sysssispackagefolders' is not supported:

;WITH cte AS (

           SELECT    cast(foldername as varchar(max)) as folderpath, folderid

           FROM    msdb..sysdtspackagefolders90

           WHERE    parentfolderid = '00000000-0000-0000-0000-000000000000'

           UNION    ALL

           SELECT    cast(c.folderpath + '\' + f.foldername  as varchar(max)), f.folderid

           FROM    msdb..sysdtspackagefolders90  f

           INNER    JOIN cte c        ON    c.folderid = f.parentfolderid


       SELECT    c.folderpath,,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg

       FROM    cte c

       INNER    JOIN msdb..sysdtspackages90  p    ON    c.folderid = p.folderid

       WHERE    c.folderpath NOT LIKE 'Data Collector%'

February 7, 2011 9:24 AM

piers7 said:

Yeah, as Chad points out it is so much cleaner and easier to do this via SMO (with or without SQL PSX)

About the only time I'd hit MSDB like this is if I had access to SQL but not admin on the host server, because SSIS used to (still does?) ship with somewhat iffy DCOM permissions that prevent non-admins browsing the package store. Or maybe in a cluster enviromnent, if I didn't want to worry about failover (or whether the SSIS service was even running at all)

February 10, 2011 8:41 AM

Zee said:

Why am I getting PowerShell script run error?

Zee - Atlanta, GA

The term 'Invoke-Sqlcmd' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and

try again.

At C:\MSSQL\PowerShell\export_ssis_pkg.ps1:8 char:27

+ $Packages =  Invoke-Sqlcmd  <<<< -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "WITH cte AS (

Param($SQLInstance = "3USAA4ARCN500")

#####Add all the SQL goodies (including Invoke-Sqlcmd)#####

add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue

add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue


$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "WITH cte AS (

  SELECT    cast(foldername as varchar(max)) as folderpath, folderid

     FROM    msdb..sysdtspackagefolders90

     WHERE    parentfolderid = '00000000-0000-0000-0000-000000000000'


     SELECT    cast(c.folderpath + '\' + f.foldername  as varchar(max)), f.folderid

     FROM    msdb..sysdtspackagefolders90 f

     INNER    JOIN cte c ON c.folderid = f.parentfolderid


     SELECT    c.folderpath,,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg

     FROM    cte c

     INNER    JOIN msdb..sysdtspackages90 p    ON    c.folderid = p.folderid

     WHERE    c.folderpath NOT LIKE 'Data Collector%'"

February 16, 2011 12:36 PM

theSuda said:

Good one!! I also saw another method to export SSIS packages using SSIS Export Column component here

March 23, 2011 6:19 AM

Max said:

Thx!!! Helped me a lot!

October 16, 2012 9:57 AM

Dennis said:

Bam! awesome script thanks big time saver

October 30, 2012 4:36 PM

Conor said:

This is great, I have been looking for something like this for a long time.  

Does anyone know how to use this same process to load the SSIS packages back into SQL?

November 2, 2012 4:11 PM

TimS said:

This is great, but when I use the script I do not get any SSIS packages that were at the folder of msdb, only if they were in a sub folder. Is there a way to fix this?



April 1, 2014 2:41 PM

Elliott said:


I had the exact same issue; in fact, ALL of my packages are at the top-level folder, so I didn't need any of the fancy CTE stuff.

I simplified the query to

"select cast(c.foldername as varchar(max)) as folderpath,,

cast(cast(packagedata as varbinary(max)) as varchar(max)) as pkg

from msdb..sysssispackagefolders c

inner join msdb..sysssispackages p

on c.folderid = p.folderid

where cast(c.foldername as varchar(max)) not like 'Data Collector%'"

and I added a line in the Foreach block: after "$folderPath = $Pkg.folderpath", insert:

if ($folderPath -eq "") { $folderpath = "MSDBRoot" }

And then let 'er rip.

April 2, 2014 6:07 PM

Mark said:

Very useful.  Thanks

August 27, 2014 6:51 AM

ManishKUmar said:

Param($SQLInstance = "localhost\SQL2")

add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue

add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue


$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "

with ChildFolders



   select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,

       cast('' as sysname) as RootFolder,

       cast(PARENT.foldername as varchar(max)) as FullPath,

       0 as Lvl

   from msdb.dbo.sysDTSpackagefolders90 PARENT

   where PARENT.parentfolderid is null


   select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,

       case ChildFolders.Lvl

           when 0 then CHILD.foldername

           else ChildFolders.RootFolder

       end as RootFolder,

       cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))

           as FullPath,

       ChildFolders.Lvl + 1 as Lvl

   from msdb.dbo.sysDTSpackagefolders90 CHILD

       inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid


select F.RootFolder folderpath, --F.FullPath folderpath, as name,

   --P.description as PackageDescription, P.packageformat, P.packagetype,

   --P.vermajor, P.verminor, P.verbuild, P.vercomments,

   cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData

from ChildFolders F

   inner join msdb.dbo.sysDTSpackages90 P on P.folderid = F.folderid

order by F.FullPath asc, asc;


Foreach ($pkg in $Packages)


   $pkgName = $

   $folderPath = $Pkg.folderpath

   $fullfolderPath = "E:\Packages\$folderPath\"

   if(!(test-path -path $fullfolderPath))


       mkdir $fullfolderPath | Out-Null


   $pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx"


October 17, 2014 4:50 AM

Rushabh Sheth said:

Thanks. This is very helpful. Awesome script.

October 21, 2015 10:51 AM

Murali said:

Hi Guys

Can you help me to list all the folders and package info under File system

your help is much appreciated.



October 26, 2015 12:44 PM

Murali said:

Sorry forgot to mention in my earlier request this is for SQL SERVER 2008 R2

October 26, 2015 12:50 PM

Sudipta said:

Is there any physical path where all the packages that deployed to SSIS catalog gets stored.

I want to export all those catalog packages but not getting  a way to do that.


Sudipta Ghosh


March 20, 2016 11:40 AM

jamiet said:


No, there is no such physical path. They are stored in the SSISDB database. I don't recall which table & column they're in but its fairly obvious if you go and take a look - I have attempted to export packages form there in the past but wasn't able to accomplish it, can't remember why.



March 21, 2016 9:20 AM

Leave a Comment


This Blog


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement