tim laqua dot com Thoughts and Code from Tim Laqua

27Jun/081

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 😉