Visibly empty packagedata field in the msdb.dbo.sysdtspackages90 table

Question

question

Tatyana asked Apr 21, '11

Visibly empty packagedata field in the msdb.dbo.sysdtspackages90 table

I'm trying to retrieve information on SSIS packages stored in the msdb database of SQL server 2005. The "packagedata" field looks empty for the most (~90%) of the packages. After convertion, CONVERT(XML, CONVERT(varbinary(max), packagedata)) some of the cells in this field start showing XML string... When I copy those ones that still look empty and paste into a notepad (or XML file in Visual studio) I can see the whole package... What is the explanation of this "invisible" data, and is there any way to make it visible without copy/paste one by one? Thank you!

sql-server-2005 ssis msdb

10 |1200

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

Answer 1 · 2011-04-22T02:45:50Z

Fatherjack answered Apr 22, '11

If you are viewing the data through the grid view in SSMS then you may have a disadvantage. Run this script DECLARE @WeirdData TABLE ( ColA NVARCHAR(10) ) INSERT @WeirdData ( [ColA] ) VALUES ( CHAR(0) + 'Hello' -- ColA - nvarchar(10) ) SELECT [wd].[ColA] , LEN([wd].[ColA]) AS [Length of column value] FROM @WeirdData AS wd go If you use Grid view then you see a blank cell under ColA but 6 under the Length column. Obviously the record has the value inserted but the grid view is unable to display it. Switch to text view, as @meltondba suggests, by pressing Ctrl + T and execute the query again you will see the column value. This is an illustration of what is happening with your data. It is simply a quirk of the grid view in SSMS

1

10 |1200

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

Tatyana commented · Apr 22, 2011 at 05:27 AM

Very good illustration! I thought it had something to do with the way packages are encrypted :) Thank you, @Fatherjack and @meltondba, you've saved me tons oftime, and I learned something new!

0 ·

Answer 2 · 2011-04-21T13:50:10Z

Shawn_Melton answered Apr 21, '11

Are you viewing it in grid view? If you change it the results to dispaly as text does it make any difference?

2

10 |1200

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

Tatyana commented · Apr 22, 2011 at 05:22 AM

Yes, it made all the difference! When displayed as text all the values are visible. I would not even think that there could be such a difference. Thank you very much!

0 ·

Tatyana commented · Apr 22, 2011 at 06:34 AM

@meltondba, - actually, I think that was the answer, but at the point when I wanted to mark my question as answered your post had a status of comment...

0 ·

question