ETL development with SQL Server Integration Services development requires an unusual blend of skills. Part T-SQL, part .Net Developer, part XML coder, etc., etc. One of the disciplines that pure coders bring to the table is the ingrained concept of version numbering. Source code versioning is the mechanism of labeling sets of code with a specific value that can be tied to releases.
Integration Services provides the capability of labeling each package with a three part version number and a comment. Here are the properties and how I use them.
- VersionMajor – Represents a significant release of a new functionality. This value is incremented manually.
- VersionMinor – Represents an addition of incremental functionality additions or corrections. This value is incremented manually.
- VersionBuild – Represents internal builds within a Major and Minor version. Each time the package is saved the VersionBuild number is automatically increased by one.
The combination of VersionMajor and VersionMinor constitute the label applied for a distinct set of functionality and features. For example, SQL Server 2008 was labeled Version 10.0 and SQL Server 2008 R2 was Version 10.5. (Internally, Microsoft decided not to make the R2 release a full major release.)
The VersionBuild number important by the development and testing groups to distinguish between different copies of the SSIS code that may be present on different workstations/servers. For example, a bug that was detected in Version 1.0.12 may have gone through multiple testing cycles before it was marked as corrected in Version 1.0.23. Note, that these intervening build #s from 13 to 22 represent code that were not released into the wild.
Sometimes you may need to change these values for all of your packages. For example, when you are about to work on a major set of functionality you would want to set the packages to the Major/Minor/Build number of 2.0.0. Then as the internal builds add up, the build value will increase in each modified package. At the end of work you may want to set all the packages to a specific build level so that they all represent a specific project level Major/Minor/Build number. Setting these manually is a royal pain. The free tool BIDS Helper can make this simple.
1. Within your Visual Studio Project, select the target packages, right-click and then select “Batch Property Update
2. Enter the Property Path and New Value and click OK.
All the selected packages will be opened and will have the new property setting. (Don’t forget to save!)
Here are the Property Path values for each of the items you might want to set. I have also included the Version Comments property so that you can provide a friendly description of the version.
The BIDS Helper tool can be used to set other properties as well. Remember, with great power comes great responsibility! Don't inadvertantly destroy your work!!