How to read SSIS packagedata field contents? (decrypt packagedata field)

If you have the package on the server, you can export it back from server easily using the SSMS.

When connecting from SSMS choose as server type Integration Services and connect to your server.

Then locate package you need, right click on it and choose Export. As Package location choose File System and select a place where you want to store the package and you are done. You have the package on your local machine.

Eventualy you can get sources of the packages using a select from MSDB. The packages are stored in msdb in the [dbo].[sysssispackages] table. You only need to cast the PackageData field corectly.

SELECT [name]
      ,[id]
      ,[description]
      ,[createdate]
      ,CAST(CAST([packagedata] as varbinary(max)) as xml) AS PackageSource
      ,[packagetype]
      ,[vermajor]
      ,[verminor]
      ,[verbuild]
      ,[vercomments]
      ,[verid]
      ,[isencrypted]    
  FROM [msdb].[dbo].[sysssispackages]

Using the above select you will have the package source XML code in the PackageSource field.

Thanks a lot!

Both the options - Exporting and the query worked very well. I tried to save the xml as .dtsx and was also able to open it in Visual Studio! Only a minor note from my end is - I didn't see sysssispackages in SQL 2005 or SQL 2008. I have the table name as sysdtspackages90

Thanks again for the timely help!

Regards Pandu