And then there were two

I’m thinking about taking some time to update RDL Documenter for SQL 2008, since RS 2008 is now becoming an indispensible part of our team’s arsenal of reporting tools.

I haven’t been looking forward to doing this, since custom report items changed for the new Designer, and it’s likely that, from a designer Widget point of view, I’m going to have to start all over.

Interestingly, I have more reason to do this than ever.  SSIS is weak in documentation tools, just like SSRS. And the general approach that I took in RDL Documenter is just as valid here.

To review that approach: shred the documentation-usable parts of the XML into something resembling XML Comments (or javadocs, on the groom’s side of the aisle), and also optionally store the information in a database, so that folks can use whatever tools they want for documenting, but any tool commonly used for commenting C# or java code should be able to comment these additional code artifacts, right off the bat.

You’re probably thinking “this is impossible, it’s so obvious, someone must have done this already.”  I thought so too. 

I checked out a bunch of tools already available, such as SSIS Documenter and, while they extract the obvious information exhaustively, they all seem to have the same missing piece: they don’t allow a developer to easily add custom tags and instructions.

What’s more, in the SSIS case, they seem to be especially weak in the only  part of the documentation I really need.  SSIS already provides a nice graphical view of the process flow, you can screen shot it at any zoom level and, assuming you’ve named your tasks reasonably, somebody can follow what you’re doing.  But the variables explorer window is not very “pliable”. What I really need is a list of exposed package variables that must be configured appropriately for my customer’s environment, with instructions for the intent, default, and use of each.

Luckily, a dtsx.config file really does isolate that exposed and configurable list of variables nicely for you. When you create the file you indicate which variables are to be included in it, and what attributes of each.  This means I can quickly create a dtsx.config file that contains a perfect subset for my documentation needs.

For a job last week, I simply used the following bog-standard piece of XSLT on a dts.config file:

<?xml version=”1.0″?>
<xsl:stylesheet version=“1.0” xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”>
    <xsl:output method=“html”/>

    <xsl:template match=“/”>
        <table class=“doc”>
            <tr >
                <th class=“docHeader”>Package Variable Name</th>
                <th class=“docHeader”>Type</th>
                <th class=“docHeader”>Example</th>
                <th class=“docHeader”>Comments</th>
            </tr>
            <xsl:apply-templates
                 select=“/*/Configuration[@ConfiguredType=’Property’
                         and contains(@Path,’Properties[Name]’)]”/>
        </table>
    </xsl:template>

   <xsl:template match=“*”>
        <xsl:variable name=“whichProperty”
                      select=“ConfiguredValue”/>
        <xsl:variable name=“descr”
                      select=“/*/Configuration[@ConfiguredType=’Property’
                              and contains(@Path,concat(‘::’,$whichProperty,’]’))
                              and contains(@Path,’.Properties[Description]’) ]”/>
        <xsl:variable name=“noDescr”
                      select =“string-length(normalize-space($descr)) = 0”/>
        <tr>
            <td>
                <xsl:value-of select=“substring-before(substring-after(@Path,’::’),’]’)”/>
            </td>
            <td>
                <xsl:value-of select=“@ValueType”/>
            </td>
            <td>
                <xsl:value-of select=“/*/Configuration[@ConfiguredType=’Property’
                              and contains(@Path,concat(‘::’,$whichProperty,’]’))
                              and contains(@Path,’.Properties[Value]’) ]”/>
            </td>
            <td>
                <xsl:if test=“$noDescr”>
                    <xsl:attribute name=“bgcolor”>yellow</xsl:attribute>
                </xsl:if>
                <xsl:value-of select=“$descr”/>
                <xsl:if test=“$noDescr”>TBD…</xsl:if>
            </td>
        </tr>
    </xsl:template>

</xsl:stylesheet>

The result was something like this (excerpted from a dtsx.config prepared the same way for the SSIS package delivered with RDL Documenter):

.. et voilà. I dumped this table into the other documentation I was preparing by other means, of the type that other tools make it easy to create.

I’ll probably elaborate on this “sketch” in the future to provide the same sort of XML Comments-shaped intermediary file, instead of an HTML table as shown here, so that SandCastle and similar tools can go to work on it.