Annotations for SSIS

All we need is an easy explanation of the problem, so here it is.

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!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Annotations for SSIS

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

Annotations for SSIS

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply