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 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.
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.
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.
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.