SSIS Quick Tip: Treating code like code–Comments

Developing an SSIS package means that you are writing code.  Developing a T-SQL Select statement, stored procedure, function, table or other construct means that you are writing code.  Developing an SSAS cube or SSRS report means that you are writing code.  I’ve said this before and I’ll say it again and again.

And yet, it seems that since it has to do with a database, or at the very least that the tool comes packaged with the database, most people believe that you don’t have to follow the normal rules of development.  Nearly every package that I have seen is devoid of comments, source control is something that is whispered of in dark corners in the hopes that others won’t notice that it is missing, and testing consists of pressing the execute button and hoping it works.  No more!  The rest of this post we will look at ways to comment our code.  I plan on looking at the other development best practices which are ignored on a regular basis in later posts.

Annotations, Comments and Descriptions

In SSIS, T-SQL, SSAS and SSRS we are given the ability to convey information to our future selves, or to those who might be unfortunate enough to follow in our footsteps.  With SSIS, we can do this through the use of good naming, object descriptions, annotations, and comments in our T-SQL or .Net code.  All of these options should be used to help define what exactly it is we thought we were doing.

Annotations

Annotations are placed on a design surface and can be added by right clicking and selecting the “add annotation” menu option.  For a long time, I tried to require my fellow developers to put an annotation on every design surface.  They are highly visible and help the next person to understand the intent of the package, the dependencies it may have, potential restart steps, etc.

CodeIsCode.Annotation.01

CodeIsCode.Annotation.02

Descriptions

Descriptions are available with (nearly?) every object in SSIS, including: variables, connection managers, containers, tasks and transformations.  They are probably the most useful of the inherent comment types of SSIS.  The only downfall to the description property is that it is not nearly as visible as annotations and thus can often times be overlooked.

CodeIsCode.Description.02

Version Comments

Another interesting package property is the version comment.  This can be used to convey information about what might have changed between versions, usually captured with the major and minor version numbers.

CodeIsCode.VersionComments.01

Comments

Last but not least are the comments that you will leave in the native code itself.  By this, I mean in your T-SQL, .net, etc.  As this is not really an SSIS construct, you use the comment designators of the given language.

CodeIsCode.TSQLComments.01

Advertisements
This entry was posted in SSIS and tagged , . Bookmark the permalink.

2 Responses to SSIS Quick Tip: Treating code like code–Comments

  1. Arthur says:

    A good point, I have probably seen hundreds of packages, guess what, most do not have any annotations at all.
    Do you know, you can also change the font and color of annotations + more?
    A comprehensive, yet mind blowing capabilities of SSIS annotations are covered in depth over here: http://2.ly/m5f4

    • Eric Wisdahl says:

      I think I had run across that article one other time but didn’t really take the time to read it thoroughly. It seems like it is a good bit of work to add the ability to the annotations. I’m also not certain how well it will deal with moving around the package, etc. Still, if you place that in a template package it is probably worthwhile as a series of placeholders for required comments or documentation of the package. I sometimes wish that the descriptions had an option to be displayed next to their objects, although I know that wouldn’t work too well with variables, connection managers, and other items not placed directly on to a design surface.

      Thanks for the link!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s