Why binary annotations?
Hello, I've noticed that text annotations are stored in the .dtsx file in a binary format. e.g. I just created an empty package and added a single annotation on the control flow surface "test" and this is what ended up in the .dtsx file: <ddscontrol controlprogid="MSDDS.Text" left="13864" top="5212" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="6000" height="1500" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="1" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d610400740065007300740000000000 " /> </control> <layoutobject> <ddsxmlobj /> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> Notice how the annotation has been serialized as binary. We are trying to document our packages by pulling this sort of information out of the .dtsx file - I don't see the point in serializing it as binary, it simply provides less information. Why are annotations serialized as binary? Is there an easy way of converting them back to text? thanks Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
February 1st, 2010 3:50pm

Annotation = Red Headed Step Child of SSIS. Really wish they'd implement some of your connect items (i.e. make them able use expressions, allow tying to objects so that they move when you reorganize the control flow, etc)Please mark answered posts. Thanks for your time.
Free Windows Admin Tool Kit Click here and download it now
February 1st, 2010 5:37pm

allow tying to objects so that they move when you reorganize the control flow Hi Eric, Actually I've had a rethink about that one. It is already possible to annotate SSIS objects because they all have a [Description] property hence what would be better would be if those descriptions were made more prominent on the design surface; perhaps there could be an option on the designer to display them in a similar way to which annotations are displayed. -Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
February 1st, 2010 6:14pm

Actually I've had a rethink about that one. It is already possible to annotate SSIS objects because they all have a [Description] property hence what would be better would be if those descriptions were made more prominent on the design surface; perhaps there could be an option on the designer to display them in a similar way to which annotations are displayed.-Jamie Agree with you Jamie. That would really make more sence.Hope this helps !! Sudeep| My Blog
Free Windows Admin Tool Kit Click here and download it now
February 1st, 2010 6:20pm

Hi Jamie,Annotations are encoded in binary for legacy reasons (due to DDS). The format is given below. // 0002000070170000dc0500000300640000000500008000000000000000002a0001000000bc0244420100065461686f6d610b0061006e006e006f0074006100740069006f006e00620000000000 // C S z f F t T // C (Color): 3 bytes encoded in RGB // S (Font Style): 1 byte encoded as S1S0: // - S1: {10 = italic, 100 = underscore, 1000 = striketrough} // - S2: {10 = bold} // f (Font Length): 1 byte // F (Font Name): variable length string encoded in CHAR (NOT UNICODE) // z (Font Size): 4 bytes // t (Text Length): 2 bytes // T (Text): variable length string encoded in UNICODE-Sergio This posting is provided "AS IS" with no warranties, and confers no rights.
February 2nd, 2010 11:37pm

Cool, thank you Sergio. My apologies but what does DDS stand for?http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
Free Windows Admin Tool Kit Click here and download it now
February 2nd, 2010 11:39pm

DDS (DaVinci Design Surface) is an internal name for the design surface we used for our designers. It is originally developed by VS and I think it was also used for a few other designers too (like the UI designers in VS 6). It is a pretty old design surface and it will soon be replaced everywhere it was used.This posting is provided "AS IS" with no warranties, and confers no rights.
February 3rd, 2010 10:28am

DDS (DaVinci Design Surface) is an internal name for the design surface we used for our designers. It is originally developed by VS and I think it was also used for a few other designers too (like the UI designers in VS 6). It is a pretty old design surface and it will soon be replaced everywhere it was used. This posting is provided "AS IS" with no warranties, and confers no rights. OK, thanks Bob. Any chance you guys could make some .Net code snippets available that would parse these binary representations for us? Thanks Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
Free Windows Admin Tool Kit Click here and download it now
February 3rd, 2010 12:10pm

Hi Jamie,I will ask my manager if it's ok to do so and work with you offline.-Sergio
February 4th, 2010 10:50pm

Jamie, Did you ever get any snippets to parse this data? Did you ever figure this out? Thanks, Cam
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2010 4:16pm

I would like to know as well :-)
October 7th, 2010 12:39am

We cannot disclose this information publicly since it's part of our main source code for the next major release. Can I understand the scenario you guys are trying to archieve?This posting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
October 7th, 2010 12:44am

Friendly reminder that this post is provider "AS IS" with no warranties, convers and no rights. Let me walkthrough with more details in the initial post I've made so you can figure the format: After you un-escape the xml in the package variables, an annotation it will be a <ddscontrol> node identified by the MSDDS.Text controlgprogid as shown below. The masterId is the parent of the annotation. The annotation contents is inside the ddsxmlobjectstreaminitwrapper as highlighted below: <ddscontrol controlprogid="MSDDS.Text" left="10360" top="2752" logicalid="8" controlid="3" masterid="0" hint1="0" hint2="0" width="6000" height="1500" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"><control><ddsxmlobjectstreaminitwrapper binary="0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d611100640064007300200061006e006e006f0074006100740069006f006e002e002e002e0000000000" /> </control><layoutobject><ddsxmlobj /></layoutobject><shape groupshapeid="0" groupnode="0" /></ddscontrol> Once you grab this string, there are some static and dynamic offsets where you can grab the annotation information. In the remaining part I will walk over some of them. 0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d611100640064007300200061006e006e006f0074006100740069006f006e002e002e002e0000000000 Color comes in the 45-49<sup>th</sup> byte which in this case is 080000. You can use Color.FromRgb to convert to a .NET System.Windows.Media.Color. Bytes 72th contains information about italic, underscore, strikethough and bold. It’s basically a bitmask where each feature (italic, underscore, etc.) will trigger a new bit as shown below: // S (Font Style): 1 byte encoded as S1S0: // - S1: {10 = italic, 100 = underscore, 1000 = striketrough} // - S2: {10 = bold} Font name length comes in the 85<sup>th</sup> byte which in this case is 6. Then the font name will come in from index 86<sup>th</sup> until 86 + fontLength * 2 == 98. The font name is highlighted here. The font is encoded in ASCII, so "5461686f6d611" gets translated to “Tahoma”. Font size is in 77<sup>th</sup> character, in our case it’s "44420100", but represents the number 0x00014244 (due to big little endian encoding and hexadecimal base). Then you divide this number by 10000.0 to get 8.25 which is the size of the annotation font. Then continue where you stopped in offset (98), the next 4 characters will contain the annotation text length “1100” gets translated to 17 (little endian encoded in hexadecimal base). Then the annotation text will start from index 102 (98+4) until index 170 (102 + 17*4). The string is 640064007300200061006e006e006f0074006100740069006f006e002e002e002e00, which is encoded in unicode characters (each character has 2 bytes, with some exceptions like surrogate chars), after converting this string to Unicode, this results in the text “dds annotation...” Hope that helps! This posting is provided "AS IS" with no warranties, and confers no rights.
October 7th, 2010 8:54pm

Sergio, thank you for your post(s), they were very helpful filling in a few of the blanks I had. Jamie, Cam, Frenk: Are you looking to read from the binary stream, build the binary stream or both? I've built methods that builds my annotations, as well as the rest of my SSIS packages and have been proto-typing some parsers. Are you looking for complete VB/C# routines or just information on what objects to reference and examples of how the methods are used? Contact me offline. V/R Paul
Free Windows Admin Tool Kit Click here and download it now
November 4th, 2010 10:53pm

Hi Paul, Sergio, Jamie, Cam and Frenk :) I am building SSIS packages using C# and the SSIS object model. This works well, but I would really like to add annotations as well as the other components. I am unable to figure out exactly how to insert the annotations once I've encoded my text in the binary string. So far I am trying this: sdn.microsoft.com/Forums/en-SG/sqlintegrationservices/thread/bebb1068-4ef6-4d60-9364-b194e172f440?prof=required is this the correct C# code? package.ExtendedProperties.Add("package-diagram", "dts-designer-1.0", [the xml]); and how much of the surrounding XML should be added as the xml variable? I've tried a lot of different possibilities - especially with the nodes: <DTS:PackageVariable>...</DTS:PackageVariable> (including all content) and <dwd:DtsControlFlowDiagram>...</dwd:DtsControlFlowDiagram> (including all content) and also just the ddscontrol element <ddscontrol controlprogid="MSDDS.Text" left="13944" top="3651" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="6000" height="1500" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="1" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d61030041004200430000000000" /> </control> <layoutobject> <ddsxmlobj /> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> ...but without any luck :( Anybody have any clues as to what the correct .net and xml is? Kind regards - Jakob
December 9th, 2010 1:51am

Okay - finally figured it out - see the code here: http://social.msdn.microsoft.com/Forums/en-SG/sqlintegrationservices/thread/bebb1068-4ef6-4d60-9364-b194e172f440?prof=required Kind regards - Jakb
Free Windows Admin Tool Kit Click here and download it now
December 9th, 2010 2:49am

0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d611100640064007300200061006e006e006f0074006100740069006f006e002e002e002e0000000000 Font size is in 77<sup>th</sup> character, in our case it’s "44420100", but represents the number 0x00014244 (due to big little endian encoding and hexadecimal base). Then you divide this number by 10000.0 to get 8.25 which is the size of the annotation font. This posting is provided "AS IS" with no warranties, and confers no rights. Can anyone explain to me how a font size of 8.25 is derived from from the endian/hex-coded "44420100". A VB or C# example would be great. I'm not familiar with endian encoding and am having trouble understanding the method being used. Thanks for your help. BillBill
December 30th, 2010 8:38am

Bill, The first thing to realize is that this whole string is a collection blocks of hexidecimal code with varying lengths in bytes (xx) concantenated together. Some of these blocks are unicode and some are non-unicode, but whose sizes (length in bytes) within the binary stream are defined by the information Sergio provided. The second thing is that this is ALL Little Endian meaning nothing more than the most signficant byte is on the end of the word or block. Where Sergio identified the font name length as 06, there is only one byte. Because the font name is in ASCII, as specified by Sergio, there is only one byte per font name letter; hence the string of 6 bytes or 6 pairs of hexidecimal digits to make up the font name. The characters of the font name are not in reverse order, just the bytes (hexidecimal digit pairs) that make up each character. When you view the text of the annotation, you'll know, because of Sergio's specification, that they are in Unicode which is two bytes. Each of these are in Little Endian where the two bytes are in reverse order; 00ab -> ab00. Before you can go diving into this binary stream you need to know what Sergio has provided is the key; if you don't know the length in bytes of the multiple pieces of information in the binary stream then you don't have any reference. Regarding Big Endian/Little Endian, that's operating system or application specific and is why sometimes you'll see one used and not the other. For Binary Annotations IT'S ALWAYS LITTLE ENDIAN. So, you need to know the length in bytes of the information you're trying to decode/encode, in this case it's 4 bytes 44 42 01 00 as well as it is little endian, so those bytes become 00 01 42 44 . Then you convert that hexidecimal value, 00014244 to decimal as prescribed. You may find the following information handy: http://msdn.microsoft.com/en-us/library/system.bitconverter.aspx If you're interested in other segments of the binary stream for building annotations that word wrap, center or right justify, background colors, persist when empty or are read only, you can find descriptions here: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/71516/ Hope this helps, Paul
Free Windows Admin Tool Kit Click here and download it now
December 30th, 2010 11:48am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics