SSIS Project Build/Release Versioning

Question

I am working for years with SSIS now and one thing I have never fully understood is SSIS Project/Build versioning. Which is not to be confused with the internal SSISDB versioning of a project. I mean it more in the sense of a release build versioning.

I'll try to explain and I'm probably going to make a bad job of it.

We have a number SSIS projects with multiple packages each and we store/execute them from catalog/SSISDB. We have a different catalog/SSISDB for each region like DEV, TEST, UAT, PROD. As one would expect.

And like in a normal SDLC we promote the SSIS projects through the regions all the way up to PROD. Like say originally we deploy to DEV from Visual Studio. Then we deploy from DEV to TEST where we want the exact same project 'release' deployed. Executed in TEST only with a different configuration/environment. So here we use SSMS to deploy to TEST from DEV.

I suppose like a Java project where you build an artifact - a WAR or EAR or whatever - with a certain version number and you promote that very same binary through the regions.

And like in Java project, if you make a change to one of the project's components/sources - in this case to one of the SSIS packages/dtsx's - it changes the make-up and version of the overall SSIS project. And I'd like this version, this release if you like, to be identifiable at this level.

I want to know that on Visual Studio I built version 1.5.1 of my SSIS project and then I deployed it to DEV and I want it to show there as version 1.5.1. And when I promote it up to TEST I want to be able to verify that indeed this is the version 1.5.1. And so on.

And when I make a new build in Visual Studio I want to be able to increment that version number. And so on.

So here is my question. Is there something within SSIS/VS that supports this kind of thing? Or would I have to work at an ISPAC file level? Say I make my build, grab the resulting ISPAC file, rename it manually or so, compile it s hash whatever, store that ISPAC somewhere like in an artifactory or source control and promote through the regions from that ISPAC file?

Or is there something within the SSIS project properties I can use for this?

Sorry, this if this a bit long winded.

Thx

Calle

P.S. I had looked at this problem before in the past with no solution. So we used a 'workaround'. We are using version control for the project files and we fudged something where we used SVN $Id commit level string replacements into the dtsx description property of each dtsx file. So after a deploy/promotion we would be able to look at a package/dtsx property in the SSISDB/catalog and identify 'ah yes, here is our new version after getting promoted'. It was not perfect since it wasnt working at a project level but it was something. Now we are using git and conceptually this property replacement workaround does not work anymore. So I am revisiting the release/build version problem.

Answer 1

Each time you save in Visual Studio, two Package properties are updated: VersionBuild and VersionGUID. The former is a monotonically incrementing number and the other is a guid.

You can manually set VersionMajor and VersionMinor and once saved, you have hit F7 (View Code) and confirm you see something like this fragment. I set Major to 11, minor to 2 and you can see I've saved this package 8 times (VersionBuild of 9)

<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
  DTS:refId="Package"
  DTS:CreationDate="7/3/2019 1:34:05 PM"
  DTS:CreationName="Microsoft.Package"
  DTS:CreatorComputerName="ERECH"
  DTS:CreatorName="HOME\bfellows"
  DTS:DTSID="{D5D7C0A7-5986-46DF-9609-501402E9E344}"
  DTS:ExecutableType="Microsoft.Package"
  DTS:LastModifiedProductVersion="14.0.3002.92"
  DTS:LocaleID="1033"
  DTS:ObjectName="Package1"
  DTS:PackageType="5"
  DTS:ProtectionLevel="0"
  DTS:VersionBuild="9"
  DTS:VersionGUID="{219699AF-3EC9-4CCA-AE51-61B813D190BE}"
  DTS:VersionMajor="11"
  DTS:VersionMinor="2">

Deployment to the SSISDB is at the project (.ispac) level so binary versioning of that takes place but and you can easily see that in project properties

The major/minor/build are not visible in the default tooling but you can tease that data out of the SSISDB with a query

SELECT
    F.name AS FolderName
,   PR.name AS ProjectName
,   PR.last_deployed_time
,   PR.object_version_lsn
,   P.name AS PackageName
,   P.version_major
,   P.version_minor
,   P.version_build
,   P.package_guid
,   P.version_guid
FROM
    SSISDB.catalog.folders AS F
    INNER JOIN SSISDB.catalog.projects AS PR
        ON PR.folder_id = F.folder_id
    INNER JOIN SSISDB.catalog.packages AS P
        ON P.project_id = PR.project_id;