Checking the Version and Build Number of an SSIS Package

Category : Articles Published : September 26, 2019 User Rating : 4.5 Stars      Views : 2.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




Introduction

When installing a new version of an SSIS package it's useful to verify what version and build has actually been installed and also what version and build it replaces. With much earlier versions of SSIS (e.g. 2008 R2) the currently installed version was very easy to check using a report within SSMS (SQL Server Management Studio). However since 2012 this is not available in a properties window or as report within SSMS but it can be checked by running a query against the SSISDB database.

This article shows how to get the current installed version and build number of a package, and also how to compare it with the version in an ispac or dtsx file.

Listing all Versions

The following query lists the current and previous installed versions of all SSIS packages along with folder and project information :
USE SSISDB
GO
--Check full version history of installed SSIS packages
--NB only works for project deployment model
SELECT folders.name [Folder Name]
      ,projects.name [Project Name]
      ,packages.name [Package Name]
      ,version_major [Version Major]
      ,version_minor [Version Minor]
      ,version_build [Version Build]
      ,project_version_lsn [Project LSN]
      ,object_versions.created_time [Installed]
      ,IIF(object_versions.object_version_lsn=projects.object_version_lsn,'Yes','No') [Latest Version?]
FROM    internal.packages
JOIN    internal.projects
ON      projects.project_id=packages.project_id
JOIN    internal.object_versions
ON      object_versions.object_id=projects.project_id
AND     object_versions.object_version_lsn=packages.project_version_lsn
JOIN    internal.folders
ON      folders.folder_id=projects.folder_id
ORDER BY projects.name,packages.name,version_build DESC,project_version_lsn DESC
On my fairly new and uncluttered SSIS server I get the following result :
SSIS version query results
I should mention that this query only works for packages installed using the Project Deployment model. Also older versions of SSIS packages may not be shown because (by default anyway) older versions do age out eventually.

As you can see from the screenshot I’ve got just one project containing one package and installed it five times. There are a few points to note about the query results :
  • Version Major and Version Minor numbers. The major and minor version numbers are manually keyed into Visual Studio at design time, so unless you are very diligent or have linked these values to your source code control system they may well not change when a package is rebuilt (I’ve used the values 666 and 777 here to make this point but the default is 1 and 0).
  • Version Build. This is a number that is automatically incremented by one each time the SSIS project is built within Visual Studio (you can also change it manually). You can see that version build 4 was never installed on this server but version build 3 and 6 were installed twice.
  • Project LSN. This is an incrementing number that is local to the SSIS server on which the project is installed. It’s incremented by one every time a project is installed.

Project Version Information in SQL Server Management Studio

As I mentioned there's no way to see the major, minor or version numbers of packages installed using project deployment within the SSMS GUI (at least I’ve never found a way) without running a query like the one above or writing a custom report. The nearest I’ve found to this is the Versions window which you can get by right clicking the project :
SSIS Versions Menu Item
This displays the Project Versions window:
SSM Project Versions Window
However there's no build number or major or minor version numbers displayed. As you can see the same 5 versions that my query returned though with just the Deployment (i.e. Installation) Time and the Project LSN.

Reconciling the Installed SSIS Version with the ispac or dtsx File Version

When I've installed a new version of an SSIS package into production I like to check that the version number is the same as that in the dtsx package file. This is fairly easy to achieve by opening the dtsx file in a text editor and looking for the version information. In my case this is as below :
SSIS dtsx file build and version
If you have an ispac file you can extract the dtsx using an unzip application (it’s basically a zip file) - you may need to take a copy and rename the file extension to .zip.

One thing to note is that the 'Project LSN' number is not listed. As I explained earlier this is specific to the target SSIS server, so if the same project is installed on more than one server the chances are that if will have a different project LSN.

Setting the Version Numbers in Visual Studio

When you create your packages in Visual Studio you can specify the Version Major, Minor, Build and associated descriptions in the package properties window. For my example the settings are :
Visual Studio Properties Window
Link back to this article : https://www.sqlmatters.com/Articles/Checking the Version and Build Number of an Installed SSIS Package.aspx

Keywords

SSIS, SQL 2017, 2016, 2014, Build, Version, Integration Services


Comments

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered