SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Display SSIS package version on the Control Flow design surface

By Herman van Midden, (first published: 2015/08/04)

Introduction

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.

The Problem

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.

 
Total article views: 3962 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Version Control with Integration Services

Thie article focuses on using SSIS to automatically check in the changes that the developers missed ...

FORUM

implementSQL Database version control..

How to implementSQL Database version control..?

ARTICLE

Ignoring Database Version Control

Steve Jones thinks version control is important, even for databases.

ARTICLE

Version Control -Part 1- Dealing with Code

Part 1 of Steve Jones series on version control and SQL Server. This article examines how you can wo...

FORUM

Storing SSIS packages to MSDB

Version Control

 
Contribute