Tim Mitchell
Follow Tim Mitchell on Twitter  Like Tim Mitchell on Facebook  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter

SSIS Catalog Project Versioning

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. For package and project storage, on the surface it might appear that each deployment to the catalog overwrites the existing version of that project or package and replaces it with the one being deployed. However, this is not the case. By default, the SSIS catalog will keep a handful of prior copies of projects in the database, which are accessible through the version management features. Prior versions still in the catalog’s history can be restored – or more specifically, marked as Current – in which case that old version then becomes the version of record.

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.

How Project Versions are Stored and Managed

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.

image

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.

image

Clicking on Versions here will open the Project Versions configuration window as shown below.

image

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.

image

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.

image

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.

image

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.

About the Author

Tim Mitchell
Tim Mitchell is a business intelligence and SSIS consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or SSIS training? Contact Tim here: TimMitchell.net/contact

5 Comments on "SSIS Catalog Project Versioning"

  1. 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!

  2. 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.

  3. Craig Lyall | May 24, 2017 at 2:30 pm | Reply

    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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: