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 :
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 :
This displays the 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 :
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 :
