x

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
more ▼

asked Feb 11, 2011 at 10:20 AM in Default

pandu gravatar image

pandu
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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.
more ▼

answered Feb 12, 2011 at 11:03 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Feb 12, 2011 at 02:50 PM

pandu gravatar image

pandu
13 1 1 1

Ah.. the [sysssispackages] table is in SQL 2008R2. As you wrote in the 2005 there is sysdtspackages90
Feb 12, 2011 at 11:39 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x943
x27

asked: Feb 11, 2011 at 10:20 AM

Seen: 4076 times

Last Updated: Feb 11, 2011 at 10:20 AM