Export SSIS from MSDB when all else fails

So I saw an interesting problem.

DTUTIL, and the interface to SQL Server management studio are broken on a production server, but no one is certain what package is currently running in production, and aren't sure how far back to look into source control to find the package with the right version number. 

Since the package is unable to be exported in a simple manner, IE using the command line or SQL server management studio, there has to be another way to get the data off of the server. 

What I did was take a reference example I have available of creating files from BLOB data and generated an SSIS package that can be pointed at a servers MSDB and extract all of the SSIS packages, with only a couple small changes for anyone's specific situation.

The basic build of the package is this:

1.  Create a connection to MSDB
2.  Create a variable to hold the file path
3.  Create a dataflow
4.  Create an Ole DB Source to the MSDB connection
5.  Use a select statement (such as the following)

select
name + cast(verbuild as varchar(255)) + '.dtsx' as filename
,packagedata -- quick row number script
,foldername

from sysssispackages packages
join dbo.sysssispackagefolders folders
on packages.folderid = folders.folderid
where ownersid <> '0x01'

6.  Use a derived task to create a fully fledged filename
7.  Create a transform script task to generate folders in the export folder if they don't already exist
8.  Use an export column transform to do the actual blob export.

Below is the example I made for this.


https://docs.google.com/file/d/0BxP3PyWTrY3FT204UjdjSUY3aDQ/edit?usp=sharing