none
save all ssis packages to file?

    Question

  • I have a couple hundred SSIS packages on my server stored in MSDB, is there a way to export all of them to a directory (by name)? I would like to take all the DTSX files and put them in source control

    thanks!


    Craig
    Tuesday, February 01, 2011 9:58 PM

Answers

All replies

  • Hiya Craig,

    Although I don't have time to actually write the code for you I am certain that you can achieve this using Powershell.

    At this blog post: http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/21/a-strategy-for-managing-security-for-different-environments-using-the-database-development-tools-in-visual-studio-2010.aspx in the "One more thing..." section I have some Powershell code that connects to SQL Server and writes out some files based on the data returned from SQL. Its not querying msdb nor writing out .dtsx packages per se but at a high level this is basically what you want to do, right?

    Hope that helps.

    -Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, February 01, 2011 10:52 PM
  • Actually I quite fnacied having a go at this :)

     

    The following Powershell script will output all of the packages in msdb to a file:

    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 -ServerInstance $SQLInstance -Query "SELECT [name],CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) as pkg FROM msdb..sysssispackages"
    
    Foreach ($pkg in $Packages)
    {
    	$pkgName = $Pkg.name
    	$pkg.pkg | Out-File -encoding ascii -FilePath "c:\temp\$pkgName.dtsx"
    }
    

    Just change "localhost" to whiever server you want to connect to!

    Hope this is useful!

    @Jamiet


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, February 01, 2011 11:46 PM
  • Couldn't help myself, had to blog it too :)

    http://sqlblog.com/blogs/jamie_thomson/archive/2011/02/02/export-all-ssis-packages-from-msdb-using-powershell.aspx

    The script in the blog post will also output the packages in the same folder structure as is in msdb which I suspect is probably useful to you!

    Regards
    Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    • Marked as answer by CraigL77 Wednesday, February 02, 2011 7:49 PM
    Wednesday, February 02, 2011 12:16 AM
  • Thanks Jamie! Will try it out ASAP.

    Thanks for making me famous! :)


    Craig
    Wednesday, February 02, 2011 4:02 PM
  • Jamie, 

    Couple corrections:

     

    1. I found quickly that this script isnt SQL 2005 compatible. I am sure you could get fancy and make it work with both 2005 and 2008, but for my purposes I just modified the table names.

    2. You forgot to append the package folder to the full folder path. I highlighted the fix in bold

     

    Thanks a bunch, this is great! Once I renamed the tables to match 2005's naming convention it worked on the first try.

     

     

    Param($SQLInstance = "<server>")

     

    #####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.dbo.sysdtspackagefolders90

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

                                                                            UNION    ALL

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

                                                                            FROM    msdb.dbo.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.dbo.sysdtspackages90 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:\SourceCode\Source Control\SSIS Packages\" + $folderPath

     

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

        {

            mkdir $fullfolderPath | Out-Null

        }

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

    }


    Craig
    Wednesday, February 02, 2011 4:24 PM
  • Jamie, 

    Couple corrections:

     

    1. I found quickly that this script isnt SQL 2005 compatible. I am sure you could get fancy and make it work with both 2005 and 2008, but for my purposes I just modified the table names.

     Aha, yes. Glad it was easy to change - didn't think about the SQL2005 case. And yes, no need to get too fancy and have it support all versions :)

    2. You forgot to append the package folder to the full folder path. I highlighted the fix in bold

     Did I? Are you sure? Check again! ;)   http://sqlblog.com/blogs/jamie_thomson/archive/2011/02/02/export-all-ssis-packages-from-msdb-using-powershell.aspx

    $fullfolderPath = "c:\temp\$folderPath\"
    
    

     

    Thanks a bunch, this is great! Once I renamed the tables to match 2005's naming convention it worked on the first try.

    Excellent, that's what I like to hear! It was fun writing it so I'm glad it helped!
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, February 02, 2011 4:32 PM
  • Hmm, I know what happened. I saw "c:\temp\....\" as your path and just typed over it with my path not realizing that $folderPath actually was a variable that gets replaced (Im not too familiar with powershell). In any other language that would be just part of the literal string ;)

     

    I stand corrected, your original code would work.


    Craig
    Wednesday, February 02, 2011 6:48 PM
  • This script exports all packages from a sql server and adds them to an existing visual studio project:

    http://microsoft-ssis.blogspot.com/2010/12/downloading-all-packages-from-your-sql.html

     

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Wednesday, February 02, 2011 7:37 PM