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!
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