Annotations for SSIS – Best Practices and Tips

sql serverssisvisual studio

Forgive me if this is an inappropriate question for this forum (being DBA and all, I thought it may fit).

I have an SSIS package that I've recently upgraded and moved from BIDS to VS 2019.

It's a very straightforward process to create and write an annotation, however, there are a number of annotations on the flow diagram which I need to edit, but I simply cannot find an option to do this. Does anyone know of a way, bar actually editing the XML (dtsx) file and manually using a text editor?

To Clarify…

This was all my fault for being a trendy developer and using a dark scheme on my VS install! It turns out that you can't see the cursor as it's black on black.

Kudos to billinkc, below, for pointing out my stupidity!

Best Answer

You've answered with your options. Either click and edit each individual annotation or open with your favorite text editor and be prepared to write XML safe comments i.e. runs when sales_amount > 30

Now that I have cracked open a 2017 version SSIS Package, the annotation XML is far friendlier for text editing than it was in the 2005/2008 era.

Given a package that looks like

enter image description here

That Inner Annotation > 30 is out in the "clear" down in the DTS:DesignTimeProperties in a CDATA field. Update all the Text attributes, save and reopen and you should be good.

    <AnnotationLayout
      Text="Inner Annotation &gt; 30"
      ParentId="Package\SEQC Placeholder"
      Size="138,60"
      Id="4b28de53-9db6-47c2-957c-21118893f6dc"
      TopLeft="34,25">
      <AnnotationLayout.FontInfo>
        <mssge:FontInfo>
          <mssge:FontInfo.TextDecorations>
            <av:TextDecorationCollection />
          </mssge:FontInfo.TextDecorations>
        </mssge:FontInfo>
      </AnnotationLayout.FontInfo>
    </AnnotationLayout>

Normally, I'd put a plug in for Biml as you can reverse engineer a package to a simplified XML markup, edit there and re-generate your SSIS package.

This is what the above package would look like (assuming I imported the SSIS Annotations which is not a default)

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="dba_288939" Language="None" ConstraintMode="LinearOnCompletion">
            <Annotations>
                <Annotation Tag="Varigence.Biml.SsisImporter.ImportedFromPackage">dba_288939</Annotation>
                <Annotation Tag="Varigence.Biml.SsisImporter.OriginalName">dba_288939</Annotation>
            </Annotations>
            <Tasks>
                <Container Name="SEQC Placeholder" ConstraintMode="LinearOnCompletion">
                    <Annotations>
                        <Annotation AnnotationType="Description">Sequence Container</Annotation>
                    </Annotations>
                    <Tasks>
                        <Dataflow Name="Data Flow Task">
                            <Annotations>
                                <Annotation AnnotationType="Description">Data Flow Task</Annotation>
                            </Annotations>
                        </Dataflow>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

However, the emitted SSIS package doesn't have annotations in it. I'll reach out to the fine folks as Varigence and see what I'm doing wrong in the package generation phase.

Visual Studio 2019 annotation edit experience

enter image description here

Related Question