question

Tatyana avatar image
Tatyana asked

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-2005ssismsdb
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.

Fatherjack avatar image
Fatherjack answered
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 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.

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 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
Are you viewing it in grid view? If you change it the results to dispaly as text does it make any difference?
2 comments
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.

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 Likes 0 ·
@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 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.