When developing and deploying a SSIS packages it is important to keep and maintain version information. Unfortunately version information is hidden in four package properties: VersionMajor, VersionMinor, VersionBuild and VersionComments. Many of us never use these. In this article I will show a simple trick to display version information on the Control Flow surface, so we immediately see which version and build we are modifying.
A SSIS package failed, and you have to correct it, so you take the last version from your version control system (VCS) and start working. But wait, is this the correct version? Did one of your colleagues maybe made some corrections yesterday, which did not make it to your VCS? Are you sure? You check the package version information (version major, minor and build) and compare it, or you just take the production package to start investigating the problem. Will you put the corrected version back in your source control system?
Wouldn’t it be nice to have the package version available on the Control Flow design Surface, so we see it when we develop a package. Seeing the current version will also remind you to update it when things change and use it in a version history description.
This can be done by using the name property of a sequence container in your control flow, and set its value using expressions. The name is evaluated whenever the package is loaded or executed and displayed.
How to do it?
Add an empty Sequence Container before the other task in your control flow (SEQC Your Control Flow). You can collapse it because we won’t add any tasks to it.
Now open the properties and find the Expressions option.
Click the ellipsis button to open the Property Expression Editor
Under Property, click the first empty row, and select the Name property from the drop down list. Then click the ellipsis button under Expressions to open the Expression Builder
Now we can chose any format for the name. Since I always use naming conventions, I stated the name string with ”SEQC” (an acronym for Sequence Container, see: http://sqlblog.com/blogs/jamie_thomson/archive/2012/01/29/suggested-best-practises-and-naming-conventions.aspx) followed by PackageName, VersionMajor, VersionMinor and the build number between brackets, resulting in:
SEQC Package1 1-0 (1)
Unfortunately SSIS does not allow ‘.’ in the Name, so I am using ‘-‘ as a separator between major and minor versions.
One has to take care that all variables values are correctly casted, resulting in the following expression:
"SEQC "+@[System::PackageName] + " " + (DT_STR,5,1252)@[System::VersionMajor] + "-" + (DT_STR,5,1252)@[System::VersionMinor] + " (" + (DT_STR,5,1252)@[System::VersionBuild]+")"
Click twice OK to close the Property Expressions Editor and check the name of the Sequence Container….
It didn’t change!!!
But check the name property of the sequence Container
The name property has changed!
Apparently Visual Studio does not always refresh the control flow display (but sometimes it does, who can tell me why?). When the package is reloaded the name of the Sequence Container will be updated.
So we immediately see the name and version of the package when we open it. Now it is up to you to set and maintain the version numbers. VersionMajor and VersionMinor should be set manually, while VersionBuild is automatically increased every time the package is executed
If we now add the version information to the error message generated (see for example) we will know in which version the error occurred, open a package from our source control system and immediately be able to verify we are correcting the correct version.