THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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]):

image

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:

image

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/

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

Comments

 

Chad Miller said:

Jamie,

Rather than directly hitting system tables, there is an API for working with SSIS: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx  

As part of the CodePlex project SQL Server PowerShell Extensions (SQLPSX) http://sqlpsx.codeplex.com, 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:

http://sev17.com/2011/02/importing-and-exporting-ssis-packages-using-powershell/

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,p.name,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

cls

$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'

  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,p.name,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 http://www.ssistalk.com/2011/03/14/ssis-export-all-ssis-packages-from-msdb

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?

Thanks,

TimS

April 1, 2014 2:41 PM
 

Elliott said:

TimS,

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,

p.name,

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

cls

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

with ChildFolders

as

(

   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

   UNION ALL

   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,

P.name 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, P.name asc;

"

Foreach ($pkg in $Packages)

{

   $pkgName = $Pkg.name

   $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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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