June « 2008 « tim laqua dot com

tim laqua dot com Thoughts and Code from Tim Laqua


Exporting SSIS Packages Stored on the Server (MSDB) to .DTSX (XML)

We wanted to export an SSIS package that was stored on the server in the msdb.dbo.sysdtspackages90 table to a .dtsx file so we could poke at it.

Here's what we came up with:

declare @SQLcommand varchar(max)
set @SQLcommand = 
    'bcp "SELECT cast(cast(packagedata as varbinary(max)) as varchar(max)) 
     FROM msdb.dbo.sysdtspackages90 
     WHERE name=''PackageName''" queryout "c:\output.dtsx" -T -c'
exec xp_cmdshell @SQLcommand

Alternatively, you can just Export the package via SSMS 😉