Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Hidden SSIS Features: Word Wrapping Your Annotations And More

By Paul Blackwell,

Probably the biggest drawback, when it comes to documenting your SQL Server Integration Services (SSIS) packages, is the inability to word wrap your annotations. Not only does having to manually insert a return (Ctrl + Enter) at the end of each line as it approaches the edge of the annotation boundary, stifle the thought process, all of that careful layout is undone if the annotation is resized. Word wrapping can go a long way to enabling the developer's thoughts to flow as they provide context and clarity to the packages they are documenting.

Over the past few years I've seen many blogs and forums which decompose much of the annotation binary stream, found in the SSIS package XML code, into its constituent components, relating the various bits, bytes and word values to the annotation properties they produce and which are found in the designer and its dialog boxes. Unfortunately, they all have left out the bits that turn on word wrapping as well as other annotation features and capabilities that developers may find useful. This article will provide additional information for exposing those features.

Standing On The Shoulders Of Giants

I've learned long ago it's important to give credit where credit is due. While I'm excited to say, "Hey, look what I found out!", some of what I am presenting here was also contained in a forum post by Sergio Clemente Filho in February of 2010, which provided some information about the binary stream for SSIS package annotations. Some of that information allowed me to focus on the last pieces I needed for this article and have paraphrased a bit of that information he provided; thank you Sergio for that post. In addition, thank you to my colleagues who have directly or indirectly contributed to this article with their insightful answers to my endless questions.

Why Annotate

Annotations can provide a clear and concise description of package control flows, data flows, event handlers and containers. They can be scoped to provide the reader the high level theory of operation, a detailed description to facilitate maintenance and troubleshooting or offer step by step instructions for filling in package templates.

The consultant's objective is to leave their client and its development team to be as self-sufficient as possible; annotated packages go a long way to help the reader understand the work that what was done and the product the client now has taken ownership of. Not only are operational packages documented with the annotations, but templates provided can are documented as well, giving development team a kick-start when new packages are created to extend or expand the BI Solution.

The package modification steps described later can be maintained in a template so that they don't have to be redone each time a new package is created. Unless your packages are developed using an accelerator that automates your SSIS Package generation with all the annotations, templates still provide a significant time savings during development as well as provide documentation consistency.

Getting Started


Consider the package annotations shown in Figures 1 and 2. This package provides a nice clean layout, it is very comprehensive and scoped to the package Control Flow segment. This package has a similar description layout and format for each data flow, event handler or container used.

Figure 1. Sample Annotation

Figure 2. Sample Annotation (continued)

It's important to maintain a clean layout that will be consistent from package to package. It can include branding of company colors, fonts, etc. Formatting of information into sections such as the Overview and Control Flow will allow readers to easily find what they are looking for.Through decomposition of the binary stream, I'll describe how to achieve the following features:Word Wrapping/Horizontal Centering/Background Fill/Editing Prevention/Tool Tips

Navigating the XML

Instead of jumping right in and modifying your existing packages, let's start with a new package so that we can better understand what we're looking for when making our changes. From a new project or an existing project, create a new package and open it in the designer. Right click the new package, located in the Solution Explorer window, and select View Code. You can also select Code from the under the View menu on the menu bar. The XML code that is the dtsx file is similar to the following code.


<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2">
<DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>
<DTS:Property DTS:Name="VersionComments"></DTS:Property>
<DTS:Property DTS:Name="CreatorName">Paul-PC\Paul</DTS:Property>
<DTS:Property DTS:Name="CreatorComputerName">PAUL-PC</DTS:Property>
<DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/23/2010 3:11:36 PM</DTS:Property>
<DTS:Property DTS:Name="PackageType">5</DTS:Property>
<DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property>
<DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property>
<DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property>
<DTS:Property DTS:Name="VersionMajor">1</DTS:Property>
<DTS:Property DTS:Name="VersionMinor">0</DTS:Property>
<DTS:Property DTS:Name="VersionBuild">0</DTS:Property>
<DTS:Property DTS:Name="VersionGUID">{6D7F7799-4636-412E-8A6D-06A18464909F}</DTS:Property>
<DTS:Property DTS:Name="EnableConfig">0</DTS:Property>
<DTS:Property DTS:Name="CheckpointFileName"></DTS:Property>
<DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property>
<DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property>
<DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:Property DTS:Name="LastModifiedProductVersion">10.0.2531.0</DTS:Property>
<DTS:Property DTS:Name="ForceExecValue">0</DTS:Property>
<DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property>
<DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property>
<DTS:Property DTS:Name="Disabled">0</DTS:Property>
<DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property>
<DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property>
<DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property>
<DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property>
<DTS:Property DTS:Name="LocaleID">1033</DTS:Property>
<DTS:Property DTS:Name="TransactionOption">1</DTS:Property>
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions>
<DTS:Property DTS:Name="LoggingMode">0</DTS:Property>
<DTS:Property DTS:Name="FilterKind">1</DTS:Property>
<DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property>
</DTS:LoggingOptions>
<DTS:Property DTS:Name="ObjectName">Package2</DTS:Property>
<DTS:Property DTS:Name="DTSID">{DAE99452-707C-485D-9866-E263BCBEC847}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName">SSIS.Package.2</DTS:Property>
<DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property>
</DTS:Executable>

Now, in the package designer, right click the design area and select Add Annotation from the pop up menu. In the annotation, type the word Test. This will provide a simple baseline to work from so that we minimize any confusion. Once you've typed Test into the annotation, deselect the annotation and view the xml code again. You'll see that two new PackageVariable nodes were added to the xml, right after the DTS:Property DisableEventHandlers property.


<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2">
<DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>
<DTS:Property DTS:Name="VersionComments"></DTS:Property>
<DTS:Property DTS:Name="CreatorName">Paul-PC\Paul</DTS:Property>
<DTS:Property DTS:Name="CreatorComputerName">PAUL-PC</DTS:Property>
<DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/23/2010 3:11:36 PM</DTS:Property>
<DTS:Property DTS:Name="PackageType">5</DTS:Property>
<DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property>
<DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property>
<DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property>
<DTS:Property DTS:Name="VersionMajor">1</DTS:Property>
<DTS:Property DTS:Name="VersionMinor">0</DTS:Property>
<DTS:Property DTS:Name="VersionBuild">1</DTS:Property>
<DTS:Property DTS:Name="VersionGUID">{6DE977C5-B1F1-49CA-B5A9-4CDEE07D8D52}</DTS:Property>
<DTS:Property DTS:Name="EnableConfig">0</DTS:Property>
<DTS:Property DTS:Name="CheckpointFileName"></DTS:Property>
<DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property>
<DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property>
<DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:Property DTS:Name="LastModifiedProductVersion">10.0.2531.0</DTS:Property>
<DTS:Property DTS:Name="ForceExecValue">0</DTS:Property>
<DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property>
<DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property>
<DTS:Property DTS:Name="Disabled">0</DTS:Property>
<DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property>
<DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property>
<DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property>
<DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property>
<DTS:Property DTS:Name="LocaleID">1033</DTS:Property>
<DTS:Property DTS:Name="TransactionOption">1</DTS:Property>
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions>
<DTS:Property DTS:Name="LoggingMode">0</DTS:Property>
<DTS:Property DTS:Name="FilterKind">1</DTS:Property>
<DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property>
</DTS:LoggingOptions>
<DTS:Property DTS:Name="ObjectName">Package2</DTS:Property>
<DTS:Property DTS:Name="DTSID">{DAE99452-707C-485D-9866-E263BCBEC847}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName">SSIS.Package.2</DTS:Property>
<DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property>
<DTS:PackageVariable>
<DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">
&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:Layout&gt;&lt;dds&gt;
&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="22437" y="12197" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;
&lt;font&gt;
&lt;ddsxmlobjectstreamwrapper binary="01000000900144420100065461686f6d61" /&gt;
&lt;/font&gt;
&lt;mouseicon&gt;
&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;
&lt;/mouseicon&gt;
&lt;/diagram&gt;
&lt;layoutmanager&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutmanager&gt;
&lt;ddscontrol controlprogid="MSDDS.Text" left="7541" top="2408" 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"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper binary="0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d610400540065007300740000000000" /&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;
</DTS:Property>
<DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">{DAE99452-707C-485D-9866-E263BCBEC847}</DTS:Property>
<DTS:Property DTS:Name="DTSID">{D078B32A-DABB-43AC-974B-CE717A3C2492}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property>
</DTS:PackageVariable>
<DTS:PackageVariable>
<DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">
&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:Layout&gt;&lt;dds&gt;
&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="22437" y="12197" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;
&lt;font&gt;
&lt;ddsxmlobjectstreamwrapper binary="01000000900144420100065461686f6d61" /&gt;
&lt;/font&gt;
&lt;mouseicon&gt;
&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;
&lt;/mouseicon&gt;
&lt;/diagram&gt;
&lt;layoutmanager&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutmanager&gt;
&lt;ddscontrol controlprogid="MSDDS.Text" left="7541" top="2408" 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"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper binary="0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d610400540065007300740000000000" /&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;
</DTS:Property>
<DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">package-diagram</DTS:Property>
<DTS:Property DTS:Name="DTSID">{3AE7A523-74AB-4929-88E9-4524A5261359}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property>
</DTS:PackageVariable>
</DTS:Executable>

I've pulled the PackageVariable nodes out so that we can focus on them. In a package there is one PackageVariable node for each precedence constraint, pipeline path, event handler and data flow task and two PackageVariable nodes for the control flow. The elements of the PackageVariable are shown below with the PackageVariableValue element value removed.


<DTS:PackageVariable>
<DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"></DTS:Property>
<DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">{DAE99452-707C-485D-9866-E263BCBEC847}</DTS:Property>
<DTS:Property DTS:Name="DTSID">{D078B32A-DABB-43AC-974B-CE717A3C2492}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property>
</DTS:PackageVariable>
<DTS:PackageVariable>
<DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"></DTS:Property>
<DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">package-diagram</DTS:Property>
<DTS:Property DTS:Name="DTSID">{3AE7A523-74AB-4929-88E9-4524A5261359}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property>
</DTS:PackageVariable>

The PackageVariableValue for both the control flow PackageVariable nodes are identical and contain xml with the < and > characters changed to < and > respectively so that they are evaluated at the appropriate time. I've replaced the < and > and reformatted the xml for our review. The xml for Event Handlers and Data Flow tasks would have similar layouts with their root node being DtsEventHandler and TaskHost, respectively.


<Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0">
<dwd:DtsControlFlowDiagram>
<dwd:Layout>
<dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="22437" y="12197" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01000000900144420100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="MSDDS.Text" left="7541" top="2408" 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="0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d610400540065007300740000000000" />
</control>
<layoutobject>
<ddsxmlobj />
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds>
</dwd:Layout>
</dwd:DtsControlFlowDiagram>
</Package>

The Binary Stream

The part of the PackageVariableValue xml that we are particularly interested in is the binary attribute of the ddsxmlobjectstreaminitwrapper node contained within the ddscontrol node with the attribute, containing the controlprogid = "MSDSS.Text" attribute. To simplify the decomposition of the binary stream, I'm going to break it into three parts; container, font and text so that we can see the controls for the various features we are going to expose. Thank you again, Sergio, for your contributions, these contributions are marked with an asterisk (*).

Container

0002000070170000dc0500000300640000000500008008000080000000002a00
The first 64 digits of the binary stream provide a collection of information about the annotation container such as width and height, foreground and background color as well as text alignment.

Header

The first 8 digits of the Container portion, 00020000, are unknown to me. They may be a marker or header indicating the binary stream is an annotation. I've changed it, went to the designer and moved the annotation, then returned to the xml only to find it reverted back to the default.

Width

The next 8 digits of the binary stream, positions 9 - 16 of the binary stream, represent the width of the annotation box. The stream value, 70170000, represents the hex value, 00001770; the 4 two digit pairs are reversed. When you convert that number from hex to decimal you get the value, 6000, which is the value of the width attribute of the ddscontrol element. Changing the value of the binary stream has no effect, however, changing the value of the width attribute will alter the binary stream when the designer is refreshed.

Height

The next 8 digits, positions 17 - 24 of the binary stream, represent the height of the annotation box and behave the same as the width. The value, dc050000, represent the hex value, 000005dc, or a width of 1500. While neither the width nor height appear to be affected by modifications in the binary stream, it may be helpful to know what these digits represent, in case you are auto generating your SSIS packages.

Unknown

The next 12 digits, 030064000000, positions 25 - 36 of the binary stream, are unknown. It is interesting to note that adornments, texts that annotate precedence constraints or pipeline paths, do not contain the 64 in their binary stream.

Background Color

The next 8 digits, 05000080, positions 37 - 44 of the binary stream, are used to control the background color of the annotation. The first three hexadecimal pairs represent the hexadecimal value of the RGB color while the last hex pair, 80, enables the custom color. To make the background red, the 05000080 would be changed to ff000000. When the annotation is selected the background color would change to red but would revert to transparent when de-selected. By default, the background is transparent; this control is described in the Transparency & Word Wrapping paragraph. To get the following colors, change 05000080 to:

  • Red = ff00000
  • Green = 00ff0000
  • Blue = 0000ff00

You can refer to other color charts to get the desired colors.

Foreground (Text) Color *

Like the background color, the text color or foreground color is controlled by the next 8 digits, 08000080, positions 45 - 52 of the binary stream. The first three hex decimals represent the hexadecimal value of the RGB color while the last hex pair, 80, enables the custom color. It is recommended that unless you're building your packages programmatically, you should use the font control panel provided by BIDS to make color changes to your text.

Horizontal Justification

The next 8 digits, 00000000, positions 53 - 60 of the binary stream, effect the horizontal centering. If the digits are changed to 02000000, then right justification is achieved. 01000000 causes center justification while all zeros, 00000000 left justification is set. I suspect that there is a combination in these hex decimals that will enable the vertical alignment, but I have not discovered it yet.

Transparency & Word Wrapping

The next hex value, position 61 in the binary stream, serves as a bitmask determining both background transparency and, the Holy Grail for annotations, word wrapping. The value 2, found in the default binary stream, can be represented by the binary value, 0010. Of the 4 bits the 3rd bit turns transparency on when it is a 1 and the 4th or last bit turns word wrapping on when it is a 1. The two bits have no effect on formatting; when a hex value greater than 3 is inserted into the binary stream the first two bits are reset to zero when the designer is refreshed.

When transparency is on, 001X, where the X can be either a 1 or 0, the background colors appear only when the annotation is being edited. Turning transparency off, 000X, will persist the background color when focus is removed from the annotation.

When word wrapping is turned on, 00X1, the text lines in the annotation that run past the border of the annotation are wrapped at the annotation boundary and will adjust automatically when the annotation is resized.

This table provides a summary of the 61st hex value
Position 61 Binary Transparency Word Wrap
0 XX00 Off Off
1 XX01 Off On
2 XX10 On Off
3 XX11 On On

Delete When Empty, Auto Sizing & Read Only

Like the 61st digit, the 62nd digit, with the value of "a" by default, controls multiple annotation features. The hex value's binary representation serves as a bitmask to delete the annotations when it's empty, enable or disable auto sizing of the annotation box, vertical centering of the text within the annotation and making the annotation read only.

This table provides a summary of the 62nd hex value
Position 62 Binary Delete When Empty Auto Size Vertical Center Read Only
2 0010 Off Off Off Off
3 0011 Off Off Off On
6 0110 Off On Off Off
7 0111 Off On Off On
a 1010 On Off Off Off
b 1011 On Off Off On
e 1110 On On Off Off
f 1111 On On Off On

When Auto Size is combined with Word Wrap, the width of the annotation will not change however, the length will grow as more text is added, wrapping the line, thereby creating more lines. When the Vertical Centering is enabled, the Ctrl + Enter is ignored and the line of text remains in the middle of the annotation box. In addition, because the Ctrl + Enter has no effect, Word Wrap is ignored when Vertical centering is enabled.

The last two hex values of the Container section of the binary stream have no apparent functionality.

Font

01000000900144420100065461686f6d61

The length of the Font segment of the binary stream varies depending on the length of the name of the font being used, however it will always begin with the 65th digit of the binary stream. If you locate the ddsxmlobjectstreamwrapper binary attribute in the font node of the diagram, you notice the value of the attribute is the same as the Font segment, above.

Script

The first two hex digits in the font segment, positions 65 & 66 of the binary stream, appear to be nothing more than a header of some sort and have no effect on the font. The next two digits, positions 67 & 68, hold the script type that's found in the Font dialog box. The default, Western, is 00.

Italics, Underline & Strikethrough*

Jumping to the 8th digit of the Font segment, position 72 of the binary stream, is the bitmask that controls Italics, Underlining and Strikethroughs. The hex to binary is as follows:

This table provides a summary of the 72nd hex value
Position 72 Binary Italics Underline Strikethrough
0 000X Off Off Off
2 001X On Off Off
4 010X Off On Off
6 011X On On Off
8 100X Off Off On
a 101X On Off On
c 110X Off On On
e 111X On On On

Bold*

The next 4 hexadecimal digits, positions 73 - 76 of the binary stream, are used to determine regular or bold text. When the value is 9001, the default, the text is regular, when it is bc02, it is bold.

Font Size*

The font size is determined by the next 8 digits of the font segment, positions 77 - 84 of the binary stream, which are 4 hex pairs. Reading these pairs in reverse order as the hex value, converting to decimal, then dividing by 10,000 will provide the font size. The default value, 44420100, represent the hexadecimal value, 00014244, which converts to a decimal value of 82,500. Dividing 82,500 by 10,000 returns 8.25 which is the default point size of the annotation font.

Font Name Length*

The next two hex digits, positions 85 & 86 of the binary stream, represent the length of the font name. The default, 06, is decimal 6 which is the length of the name Tahoma.

Font Name*

The next pairs of hex digits, as determined by the Font Name Length, represent the ASCII number of the Font Name. These are Non-Unicode values.

Text

0400540065007300740000000000

The beginning of the Text segment of the binary stream is dependant on the length of the font name used for the annotation. In this discussion, the text segment begins at position 99 of the binary stream and will change as the length of the font name changes. The following sections describe the sub-components of the Text segment

Text Length*

The length of the text is represented by the first 4 hex digits and is read in pairs in reverse order. The length of our text is represented by 0400 which translates to 0004 in hexadecimal or 4 in decimal.

Text*

Except for the trailing 8 digits, the remaining digits are the characters in the text in Unicode. Each group of 4 digits represents a Unicode value in two groups of two digits in reverse order. For example, our text, Test, would be decoded as follows:

Binary Stream to Character Translation
Binary Stream Hexadecimal Decimal Character
5400 0054 84 T
6500 0065 101 e
7300 0073 115 s
7400 0074 116 t

Other Features

In addition to controlling the annotation through the binary stream, other features can be added through the attributes of the ddscontrol node. For example, along with disabling Editing through the 62nd binary stream bit, you can also make the annotation non-selectable by setting the selectable attribute to 0. However, making the annotations non-selectable could be troublesome should the Auto-Layout be inadvertently performed. You will have to go into the xml and re-enable selectability before you can select your annotations to layout.

Also, you can add a tooltip attribute in the beginning of the ddscontrol node to provide information or instruction to the reader when they hover over your annotations.

Finding Your Annotations in the XML

Because we started with a clean package, it was very easy to find the controlprogid="MSDDS.Text" attribute which helped identify the annotation. However, when you have many tasks in your control flow and event handlers, and data flow tasks containing several components, you're likely to have a lot adornments, annotations on the precedence constraints and pipeline paths that cannot be edited. These nodes, the adornments, typically have a masterid that refers back to a controlid of a polyline node and will have the value of 2500 or 3500 at the end of the container segment, positions 61 - 64 of the binary stream.

Adding Branded Annotations

What is meant by "Branded" is the use of a company color scheme, typeface, etc. and in this first example I will show how to apply a color schemes with word wrapping and a predetermined width. Let's create a format as shown in the next Figure which requires a header and its subsequent text using our color scheme, presetting our annotation to word wrap at a predefined width.

In the Design view of the package, right click in the designer area and select Add Annotation from the pop-up menu, then type Header.

By default, this will create an annotation that has a width of 6000 and a height of 1500, having a font of Tahoma with regular typeface and size of 8 (actually 8.25) whose text is Header. In the XML, the annotation is represented by the three segments of the binary stream, described earlier, as follows:


Container
0002000070170000dc0500000300640000000500008008000080000000002a00


Font
01000000900144420100065461686f6d61


Text
060048006500610064006500720000000000


We are going to change our text color to blue (RGB = 005288), keep the same Tahoma font, but use the Bold typeface for the header with a size of 12. In addition, we'll set the width of our annotations from 600 to 13000.


Using the information provided earlier, the three segments of the binary stream will change to accommodate the desired formatting. The width portion of the Container segment will change from 70170000 to 50320000, the text color portion, from 08000080 to 00528800 while word wrapping will be turned on changing 2a00 will change to 3a00. In addition, the typeface portion of the Font segment will change from 9001 to bc02 while the size portion will change from 444201 to c0d401. The text will have no changes at this time. The resultant segments of the binary stream will be changed as follows:


Container
0002000070170000dc0500000300640000000500008008000080000000002a00 - Old
0002000050320000dc0500000300640000000500008000528800000000003a00 - New


Font
01000000900144420100065461686f6d61 - Old
01000000bc02c0d40100065461686f6d61 - New


Text
060048006500610064006500720000000000


In the XML file of the package, we're going to do a Find and Replace of our binary stream. We kept our text relatively small so we shouldn't have an issue with a string that is too long. In the package XML, open the Find and Replace, select Quick Replace and enter the following:


Find what:
0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d61060048006500610064006500720000000000


Replace with:
0002000050320000dc0500000300640000000500008000528800000000003a0001000000bc02c0d40100065461686f6d61060048006500610064006500720000000000


If all was entered properly, you should go right to your annotation. Remember, for the Control flow, there are two Package Variables to create the annotations, therefore, expect to see two replacements. If you created your annotation in a Data Flow or Event Handler, you'd make only one change. Click back on your Designer and notice the header annotation has changed.

Create another annotation as before, but this time, enter <<Text>>.

For the text portion of our annotations our color scheme will be the same as well as the use of the same font with a regular type face, but change the size to 10. In addition, the <<Text>> annotation will be the same width as the header and word wrap. After building the binary stream in accordance with the earlier sections, the resulting Find and Replace values are as follows:


Find what:
0002000070170000dc0500000300640000000500008008000080000000002a0001000000900144420100065461686f6d6108003c003c0054006500780074003e003e0000000000

Replace with:
0002000050320000dc0500000300640000000500008000528800000000003a00010000009001a0860100065461686f6d6108003c003c0054006500780074003e003e0000000000


Once you have formatted your annotation header and text containers, you can begin modifying the text as you wish.

After a bit of formatting such as aligning the lefts, reducing the heights of the Header with the bottom select handle and reducing the vertical spacing between the two annotations we get this:



You can paste documentation from another source without affecting the word wrap, color or any of the other features that were just changed.

While this may seem a bit tedious, once you are comfortable with working in the XML, you can create several Header and <<Text>> annotations in your package control flows, data flows, event handlers and containers, open the package XML documents, then execute the Find and Replace on all open documents.


If your development team uses SSIS templates or specific design patterns, you can add these basic annotations for developers to use when they open the templates for editing. These template SSIS packages can be saved to the directory

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\


Or if you're working on a 64 bit machine they can be saved to the directory


C:\Program Files(x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\


When you open a new SSIS package for editing these template packages will show up in the list of templates.


Warning Label

The warning label will demonstrate the use of background colors, auto grow as well as centering of text. In addition, we'll add a tooltip to our label.

Let's first start by adding two annotations, the first containing the text Warning and the second the initial text or warning as shown below.

We will not change the font typeface, size or color in the binary stream but rather use the Font dialog box after the background color and centering have been set. For this demonstration we'll use standard red and yellow colors to keep the editing simple and edit the binary stream directly.

Going to the XML it should be easy to find the binary stream that we will be editing. First of all there are only four in our package, and these two new annotations will have the default Container segment that we've identified before as seen below.

In accordance with the earlier section on Background Color, we find the 37th - 44th bits in the binary stream as shown below

Using ff0000 for the color red and changing the last pair, 80 to 00, we get the following modification to the binary stream

Now we'll continue and enable the word wrapping as we did before as well as center justify our text as described earlier in this article. This is done by altering the three hexadecimal digits shown below

The new value is 11a which will turn on center justification as well as turn off transparency as described earlier as follows

As I said earlier, these changes will have to be made to the second annotation in our warning label as well as this set of changes made twice because the control flow has two sets of annotation binary streams for each annotation. So, now that we have on binary stream edited to the point we want to edit it, we'll take those changes and copy them to the other three spots that need to be change using the Find and Replace. So we'll highlight the modified Container segment of our edited binary stream and copy it as shown below:

Next, highlight the Container segment in the binary stream below as that will be modified, then select the Find and Replace from the Edit menu.

Click the Replace All and there should be 3 replacements. Once you have made the replacements, click on the package Designer to view the changes that were made. They should look as follows:

Next, using the Font dialog box we'll make updates to the typeface, size and color of the warning title and text. Right click each of the warning annotations and make the following Font settings for the Warning Label and warning text, respectively:

After aligning the left side of the two annotations, shortening the label height, adding the warning text and removing the vertical space between them, you'll get the following warning box:

When adding text to the label you'll notice how you have to keep adjusting the height. Now we'll enable the auto height by changing the 011a to 011e for the four entries that define our two warning labels.

In addition, let's add a tool tip.

The final result that we see when we switch back to the designer:


You can add a Company Copyright annotation and disable selectability and editing as described earlier as well as any number of other features or combinations thereof.

Summary

Understanding the structure and capabilities of Annotations can go a long way whether your providing minor configurations to existing packages or templates or your generating your SSIS package annotations from an application. When used with their full capabilities, annotations can provide a professional look to your work making your most complex packages easy to understand as well as maintain.

While some of these adjustments may seem a little tedious, saving them in your team's template collection can go a long way in speeding up your package documentation process.

Resources:

Package1.dtsx
Total article views: 6777 | Views in the last 30 days: 51
 
Related Articles
FORUM

Migrate multiple DTS packages from one server to another ?

During DTS Packages migration, any better method to reset connection strings, and logging properties...

BLOG

SSIS Package Annotation in Denali

If you are anything like me, you document your SSIS packages for those who may have to maintain them...

FORUM

Annotate text SELECTIVELY in Value property of a textbox in Report Designer

Is it possible to add annotation (italics, bold face etc.) to just PART of the text in the Value pro...

FORUM

Accessing Dynamic Properties task Properties in Activex Script

DTS Package/ Dynamic Properties task

BLOG

Change Package Properties Using Parameters–SSIS 2012

In DTS, changing package properties was close to impossible (the less we talk about DTS, the better)...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones