If you’ve spent much time working with the SSIS catalog, you likely already know that the catalog is where all assets related to package storage, configuration, and execution are stored: all of the projects and their associated packages, configuration environments, and execution logs are all stored in the SSISDB database used by the SSIS catalog. When deploying a new package or project to the SSIS catalog, the binary code and metadata is stored in the SSISDB database, which is the storage home of the SSIS catalog. Within there, you will find SSIS catalog project versioning, in which past versions of the source code are automatically persisted in the database.
In this post, I’ll walk through the essentials of the SSIS catalog project versioning: what it is, where it works well, and how to use it.
SSIS Catalog Project Versioning
Project versions are stored in a catalog table called internal.object_versions. This table stores the metadata (created date, deployed by, etc.) as well as the actual binary data for each project. The number of versions retained in this table is limited on a per-project basis, and is configured at the catalog level. As shown below, one of the settings in the SSIS catalog is the maximum number of versions per project, set to 10 by default; the maximum number of versions per project can be set to any whole number between 1 and 9999. When the setting Periodically Remove Old Versions is set to True (also the default), a SQL Server Agent job will run to clean up old versions of non-current projects in the SSIS catalog.
By default, the most recently deployed version is set as the current version. However, you can promote a prior version to the current version.
Version restores are nondestructive metadata operations. More specifically, when you restore a prior version of a project, it does not overwrite or delete the current version; rather, a version restore simply points the marker for the current version to a different row in internal.object_versions. When a project version is marked as Current, it becomes the version of record for that project. Any operation related to that project – including execution, exporting, setting up environment references, etc. – will use the version marked as current for its basis of those operations, regardless of whether that version is the one most recently deployed.
SSIS stores its assets at the project granularity. What this means is that individual packages are technically not stored in the catalog, but they are part of the larger project object which acts as a container for the packages. The maintenance impact of this architecture is that versioning only exists at the project level; it is not possible to restore a single package without restoring the entire project. This is even true in SSIS 2016: even though that version will allow you to deploy a single package, behind the scenes the SSIS catalog still stores all of the packages in a project as one entity and still exposes the versioning only at the project level.
Use Cases for Project Versioning
Although you probably won’t go through the version restore process on a daily basis, there are a few specific cases where it comes in very handy:
- Regression testing. This is my favorite use of SSIS catalog project versioning. In a testing environment, you can deploy a new version of a project, run a test load, then restore the prior version and perform another test load, and compare the results. This makes easier the process of testing the behavior of two different versions without requiring a redeployment between each test.
- “Oops” deployments. Most everyone (including me) has done this at least once: You think you’re deploying to Test but you’re instead pointed to Prod. The versioning feature of the SSIS catalog allows you to quickly roll back to the correct version without having to redeploy.
A question I’ve gotten in the past is, “Is SSIS project versioning a substitute for source control?” The answer to that is an emphatic No. Although project versioning does have some really cool uses, it’s a terrible source control tool. Keep checking your SSIS code into your source control system, and reserve the use of project versioning to use cases similar to the above.
Restoring a Project Version
Project versioning doesn’t really stand out in the UI of the catalog – you have to go looking for it. To open up project versioning, right click on a project in the SSIS catalog and find the menu item labeled Versions.
Clicking on Versions here will open the Project Versions configuration window as shown below.
Reviewing what is shown above, we have a list of versions of this project that are currently stored in the SSIS catalog. The versions are listed in descending order of the deployment time, and unless a prior version has been restored, the most recent version will be marked as current (indicated by the check mark in the Current column). As shown in the example above, none of the prior versions of this project has ever been restored, since the Last Restored column is blank for each of these.
Any of the prior versions of this project stored in the SSIS catalog can be restored to the current version. To restore a prior version, select that version in the UI as shown below, then click the Restore to Selected Version button.
After clicking the Restore to Selected Version button, you’ll be prompted to confirm the operation. If you select Yes to confirm, the prior version selected now becomes the current version. As shown below, the more recent version of the project is still in the list of available project versions, but it is not marked as Current. The prior version (with a project LSN of 6, called out below) is marked Current, with a Last Restored date of when that was set to the current version.
As mentioned earlier, restoring to a prior version does not overwrite or otherwise invalidate the more recent version; it simply marks it as not current. Project versions not marked as Current will remain in the catalog until the maximum number of per-project versions is exceeded, at which time older non-current versions will be purged to stay at that maximum number of versions.
When you deploy a new version of the project to the SSIS catalog, that deployment operation will automatically mark the newly deployed version as the current version. As shown below, after deploying a new version of this project, the new version is marked as Current.
The UI makes it apparent which versions were deployed and restored and when those events took place. What does this look like in the underlying catalog tables? There are two key tables impacted when a different version of the project is restored:
- internal.object_versions: This table stores all of the versions, both current and prior, for projects stored in the catalog. When a project version is restored, the restored_by and last_restored_time fields are set to the user ID and current date/time, respectively.
- internal.projects: This table stores metadata about each project. When a project version is restored, the object_version_lsn is updated to point to the current project version LSN (which is defined in the internal.object_versions table above)
Conclusion
SSIS catalog project versioning is a somewhat hidden yet very useful tool for version management. Although not a substitute for source control, versioning in the SSIS catalog is useful in testing scenarios or to quickly recover from accidental deployments.
On e thing that has caused us a lot of headaches is SSIS versions.
Specifically it is impossible to identify which version of a project is installed. In a standard .net application you have major/minor and build version numbers which you can see whn you look at the properties but in SSIS, as you can see in the screen shot above, you have NO idea what the version is, just when it was deployed. A totally pain. especially as we may deploy this package to 4 or 5 different (testing) servers before deploying to live and making sure that they are running the correct version is basically impossible.
We have had to resort to making sure that the developers put the build date/time in the project description field!
Thanks for this article, I wasn’t aware of this feature. Is this function available package level? I’ve heard that 2016 has package level deployment, but I won’t be there until next year.
@adelio You can query SSISDB for the latest deployment of each Project. I use the query
USE SSISDB
SELECT object_name, created_by, MAX(created_time) AS Last_Deploy
FROM SSISDB.catalog.object_versions
GROUP BY object_name, created_by
I’m working this morning on a similar query to show the most recent Build number for individual packages. It’s readily available in SSISDB, though I don’t see an easy way to crossref against TFS/SSIS changeset UI. The only way I see can see the package build number seems to be in the XML code of dtsx.
Hi Charles, the storage in the SSIS catalog is still at the project level in 2016. Even though you can deploy a single package now, the versioning feature works at the project level, not the package level. However, if you wanted to extract an older version of a specific package, you could restore the project version, export the entire project (which allows you access to all of the packages therein) and then restore again to the newer version.
For SSIS 2016, I don’t see any way to delete a single package from the Catalog? Do you have any insights? Thanks,
Craig
Hi Craig, unfortunately there isn’t. To delete a single package, you’d need to delete that package from the SSIS project source code and then redeploy.