SQLErudition.com

There are many schools of thought about naming conventions, not just about SSIS variables, but about naming anything that can be given a name! One point that everyone agrees on is – use a convention that suits your environment, and stick to it.

One issue that I face while looking at packages with many variables is that there is no straightforward way to get a summary or a quick glance of all variables that have expressions defined for them. The BIDS Helper add-in for Visual Studio assists in this regard by adding a triangular icon overlay over the variables with an expression. SQL Server 2012 is also said to have a similar feature but I haven’t tried that yet. On the other hand, if I need to see a summary of variables that have values coming from a config file, I have to use the Configuration Editor to edit the config file and reach its last summary screen.

I’ve started using a prefix-based convention to name my SSIS variables recently based on the origin of the variable value. I distinguish between the variables as having –

1. a value set from a config file at execution time.
2. a value calculated in an expression at execution time. The expression could be based on other user variables, system variables, functions or literals.
3. a static value that will not change from its design time value.
4. a value assigned in a Script Task, a For Each Loop Container, Row Count etc.

If I know at design time that the value of the variable will be passed from a configuration file, I give it a prefix of “config”, e.g. configEmailDistributionList. If the value would be set via an expression then the variable gets a prefix “expr”, e.g. exprDirectoryForArchive. Other variables do not have any prefix, simply a name that is descriptive enough.

Advantages:

• At the time of creating a config file, the prefix makes it clear that all variables with a “config” prefix must be included in the configuration. So the config file now has only such variables along with other task or connection manager properties.
• On opening up a package, I can easily figure out where should I be looking for the value of the variable, the config file or the Expression Editor.
• Alphabetical ordering in the Variables window groups similar variables together.
• Helps in development because I don’t have to constantly check back variable properties if it has an expression or not.
• Helps in standardizing the names and expressions for some of the most frequently used variables.
• Helps in debugging.

Disadvantages:

The argument against this convention would be the same as given for any prefix-based convention (e.g. Hungarian Notation) that if the origin of the variable value changes then the prefix would also need to change. Which in turn, may need a change at all those places where the variable is used. As an example, if a variable was getting its value from a config file but would now have a static value, then the “config” prefix would have to go away and all the expressions, properties etc. using that variable would have to be updated. Right now I’m counting on doing a Find/Replace in the XML code of the package.

I’ll try to see if using variable Namespaces would be a better idea than prefixes and share the experience in a blog post later.

References –

(not specifically for Hungarian Notation, but in context of any prefix-based convention)

Hungarian notation (Wikipedia)
http://en.wikipedia.org/wiki/Hungarian_notation

Hungarian Notation (MSDN)
http://msdn.microsoft.com/en-us/library/aa260976(v=VS.60).aspx

Hungarian Notation – The Good, The Bad, and The Ugly
http://ootips.org/hungarian-notation.html