SQL SERVER – Getting Started with Project Versions in the SSIS Catalog – Notes from the Field #106

[Notes from Pinal]: We are human and we make mistakes. However, sometimes mistakes are so big that we can’t reverse it. Version control is our rescue when we make mistakes. In my life I have been fortunate few times when I deleted something important and version controlled saved me. Similarly, for SSIS Catalog version control is very important as well.

In this 106th episode of the Notes from the Fields series database expert Tim Mitchell shares very interesting conversation related to how to do project versions in SSIS catalog .

A handy but frequently overlooked feature of the SSIS catalog is project versioning. When the SSIS catalog is installed on a server, by default the catalog is configured to store a limited number of previous versions of each distinct SSIS project deployed to the catalog. Not only does the catalog keep those versions, but the SSMS interface makes it easy to browse – and even revert to – previous versions.

In this post, I’ll show how you can use the version history feature in the SSIS catalog to access previously deployed versions of a project.

Accessing the Version History

The fact that the version history is available doesn’t really stand out when you’re browsing the SSIS catalog. To find it, you have to right-click the project in question, and in the context menu you’ll see the option to show versions as indicated below.

When I access the version history for my project named Testing SSIS Packages, the following dialog is shown. The version history here indicates that this project has ten different versions stored in the history (the default maximum – more on that momentarily). Also, the most recent version is the current version, as indicated by the check in the Current column on the far left.

Although this interface does not show what has changed, it does indicate when each version was deployed. There is also a Last Restored column, indicating if an older version was ever restored. By default, the Last Restored value will be null, until and unless that version is changed from a historical version to the current version.

Why Restore?

In a moment, I’ll demonstrate how to restore an older version of a project. First, though, I like to start with the why before I show the how. Why might you want to restore an older version of a deployed project? There are a few cases when this might be useful, but one of the most practical is for regression testing. Let’s say you deploy a project to your dev/test server (and you’re always testing before deploying to production, right?). If there is a question about a new version of an SSIS project, you can very easily revert to an older version and compare the old behavior to the new behavior. The other obvious case is simple human error – someone mistakenly deployed an incorrect or not-yet-ready project, which could be easily remedied by rolling back using the following method.

Rolling Back to a Prior Version

Reverting to an older version of a project is very easy. In the Project Versions dialog box, simply select the version you want to use as the current version and click the Restore to Selected Version button. You’ll be prompted to confirm that you want to do this, and if you answer in the affirmative, the selected version will then become the current version. As shown below, you can see that I have restored to a month-old version of the project, as indicated by the Current check box and the Last Restored date.

You might ask, what happened to the version I just replaced? Don’t worry – it’s still there. This does not do a restore in the same way a relational database is overwritten when it is restored. Rather, a configuration setting stored in the SSIS catalog simply points to the Project LSN value of the current version for each project, which may or may not be the most recently deployed version. When a package in this project is executed, the plumbing within the SSIS catalog will execute the version marked as current.

I can easily undo the change I just made by selecting the most recent version again, go through the restore exercise again, and we’re back to where we started.

As shown above, we’re back to where we started – the most recent version is the current version, and that version will be used when package(s) in that project are executed. The only difference is that we can see – by referencing the Deployed Time and Last Restored values – that the prior release version (Project LSN = 15) was temporarily restored, and then replaced as current by the more recent version (Project LSN = 16).

One last note on restoring a project: Because the code deployed to the SSIS catalog is grouped at the project (not package) granularity, it is not possible in current versions of SSIS to restore just a single package. However, in SSIS 2016, package-level deployment will return, which will change the behavior of restoring project versions.

Version Retention

As shown in the previous example, there are exactly ten historical versions of this project stored in the catalog. That is no accident – the maximum number of project versions is set to 10 by default. This is a configurable value, however. If you open the catalog properties window in SSMS, you’ll see that the maximum number of stored versions can be configured.

There are two configurable properties here: the maximum number of version to keep, and the Boolean value to enable the periodic removal of old versions. I have rarely found a need to stray from the default settings, but they are available for modification should you need to do so.

This Is Not Source Control!

One final though on versioning: Please don’t use this as a substitute for real source control! Just because SSIS will store the version history, it doesn’t mean you should use that as a means for source control. The versioning functionality is intended as an administrative tool for logging, testing, and if necessary, emergency rollback. The SSIS catalog version store does not have most of the features of a full-service source control tool. Therefore, you should be checking in your code to a proper source control repository even if you maintain the version history in the SSIS catalog.

Conclusion

The version history feature in the SSIS catalog is one of the best-kept secrets of this product. Although you hopefully won’t have to use this on a daily basis, it is a very handy tool to have at your disposal.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)