SQL Server 2012 SSIS Project Versions

As you may already know, Integration Services Catalogs in SQL Server 2012 capture versions of deployed projects. Indeed, Integration Services Catalogs allow you to revert to an earlier deployed version. The question that you may ask is how do you know which version to revert back to. This article discusses a few things around Project Versions feature in Integration Services catalog and how it does not capture some of the Project Properties that you can set in SQL Server Data Tools (SSDT).

Firstly, just a little disclaimer: I think Integration Services Catalogs Project Versions feature cannot compare with source control system – I do not think it is meant to replace one either. This article is meant to highlight what it currently offers and a few thoughts around its usage that I have found useful. You may find different ways of using it, or none at all.

Another small note worth mentioning – I would like to thank a comment from Leonardo Matos on my earlier blog post called, SSISDB Bug: Package Parameter Mapping, where a question on which version to revert to from Integration Services Catalogs should be used.

Integration Services Catalogs Project Versions

A deployed SSIS project versions can be accessed from Integration Services catalog in SQL Server Management Studio (SSMS) by right clicking on the project, as shown below.

The Project Versions window will appear as shown below.

Here you see there are five versions, the latest 2 have Description values. This Description value can be changed in the Project Properties in SSDT which will be discussed later in this blog post.

SSIS Project in Visual Studio 2012 – Some Quirkiness

In SSDT (both Visual Studio 2010 and 2012 versions), you can modify Project Properties. However, not all values you set here will be saved, let alone carried through to the deployed project in Integration Services Catalogs.

To access the Project Properties, simply right-click on the project node in Solution Explorer within SSDT – or go to the menu and click on Project > Properties.

Only the following details are included in the deployment:

• CreatorComputerName
• CreatorName
• Description
• Name
• ProtectionLevel

All information under Version section in this window is not included during deployment. In fact, values that are in VersionComments, VersionMajor and VersionMinor are written to the underlying dtproj file, but not being read back by the GUI. What makes it even interesting is that after the initial change (the first time you sent value to VersionMajor, VersionMionr and VersionComments), the dtproj file is not being updated with the new values.

For the purpose of checking some values – seeing if they are at all carried across when the project is deployed to the server, I set them to some values to check easily.

I have purposely set VersionBuild to some random and somewhat high number to easily find it.

Investigating SSISDB

SSISDB database contains a number of tables and views relating to projects and packages.

Here are some of my questions when investigating if any of the information under the Version section is captured anywhere in the database.

1. Is there any column related to “version” captured somewhere in the database?

I use SQL Search (it’s free! – at the time of writing) to easily search for tables that may contain the version number that I have set.

The outcome is a number of tables and views relating to internal.object_versions, internal.projects, internal.packages, internal.executables and internal.object_parameters. However, when I inspect these tables or views, they do not contain the values I am after – at least not the VersionBuild value.

Note: I’m using SQL Search v2.0.1.8 here, where wildcard search is now supported! For more details on this, please visit Red Gate’s SQL Search v2 Release Notes.

2. Could the version build value stored as a different name in the database?

Here, I use SSMS Tools Pack to search for the value “11239” (relating to Version Build). The outcome is not so good – no record found.

I then repeat the search for values that I have set (recorded on dtproj file but not shown via the Project Property Pages window in SSDT). The result is the same, there is no record related to them at all.

Note: You can inspect SSISDB using your own script rather that using the tools above, if you prefer to.

What’s In The ispac File?

Within the ispac file, there is a project manifest “file” where it stores Version information similar to the dtproj file. Here I also found out that the Version Build, Version Major and Version Minor do not correspond to dtproj file or the ones set via SSDT.

To get to the Project Manifest file, change the ispac file’s extension to zip. In this zip file, there will be @Project.manifest as shown below. I also tried changing values in this file manually to see if the changes would make it to SSISDB – unfortunately the answer is no.

Back To The Original Question

So, the original question was how do we know which version to revert back to? Well, there are several things that you can do to mark a deployed version, such as Description of the Project Properties in SSDT prior to deployment. Or if you have deployed it, you can keep track of the Project LSN.  Please let me politely remind you that I believe Integration Services Catalogs Project Versions is no replacement for source control system. So, I would recommend you to use TFS, Visual Studio Online or other source control system to keep track of your changes and deploy from there.

I find he Project Versions feature of Integration Services is handy for auditing purposes. As an example, if you have accidentally overwritten an SSIS project on the wrong server, you could revert back to the prior version quickly. I also find it most useful for Development environment where I am performing testing or debugging.

My investigation stops here. The information on project versions may have been captures somewhere that I don’t know of. So if you have found a way of retrieving the values that is set in Project Properties via SSDT, please let me know. I’d love to hear from you.

Wrap Up

SQL Server 2012 Integration Services Project Versions feature is a little immature right now. It gives provides a level of auditing. There are several Version properties that can be set in the dtproj file that do not seem to be included when deploying the project to the server.

I have used SQL Search and SSMS Tools Pack to help me quickly investigate this issue.

I have also raised a Connect item here in regards to the Version properties in SSDT. It would be great if you could vote for improvement of this feature.

Further Reading

If you would like to learn more about the new enhanced features of SQL Search, please visit my #SQLCoop mates’ blog posts here:

New and Improved SQL Search By Red Gate by Mickey Stuewe

SQL Search: The indispensable tool just got better by Jeffrey Verheul

RedGate SQL Search Has What???? by Chris Yates

To learn more about SSMS Tools Pack, read my previous blog post here.