question

pandu avatar image
pandu asked

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

Hi, I have an SSIS package installed on the server. Unfortunately, I don't have the corresponding SSIS package source. Is there a way to get the package source from the encrypted field packagedata? The .dtsx file could not be found anywhere on the server. :( Nobody knows where else it is... Please help Thanks in advance Regards Pandu
ssispackage
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pandu avatar image
pandu answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ah.. the [sysssispackages] table is in SQL 2008R2. As you wrote in the 2005 there is sysdtspackages90
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.