Thursday, May 10, 2012

SSIS msdb.dbo.sysSSISPackages

My coworker and I are in the process of converting a bunch of 2005 SSIS Packages to 2008 SSIS.

These packages are stored in the msdb database in the dbo.sysSSISpackages table.

The following msdn link shows all the columns and their descriptions:

We needed to get to the raw XML for one of the packages as it didn't appear to be in our source control.  The above link told us that the code is stored in the column PackageData.  Unfortunately, it appeared to be encoded.

After a bit of searching I found the following code to convert the code back to the XML.

-- Get XML from encoded column


, CONVERT( xml, CONVERT(varbinary(max),PackageData) ) 
   AS XMLSource

FROM msdb.dbo.sysssispackages