SQL Server 2016 brings some new features for Integration Services such as AlwaysOn, and Custom Advanced Logging. You’ve probably read about these features in other blog posts, If you haven’t I encourage you to read some of them, like this. These changes also comes with some changes in SSIS Catalog, in this post I’m going to explore some of changes in SSIS Catalog 2016 for you.
What is SSIS Catalog?
SSIS Catalog is a repository model for metadata of SQL Server Integration Services. This repository is a SQL Server Database which introduced in SSIS 2012. This catalog appeared with the big change in deployment model of SSIS named Project Deployment Model. In this database there are tables for packages, and executable objects, as well as some other useful metadata information. SSIS catalog stores information about deployments, and other operations such as execution of packages. SSIS Log will be also stored in SSIS Catalog (if you use the new logging method introduced in SSIS 2012, not the legacy SSIS logging).
SSIS Catalog uses many Views and stored procedures to manage the operations of SSIS. I’ve previous explained in a series of posts about SSIS Catalog that what are the most common tables and objects in SSIS, and their usages, and I’ve mentioned few helper tables to add to the catalog for better reporting on top of it. I strongly recommend you to read it here.
What’s New in 2016 Catalog?
Tables and Views
There are only two tables so far added to SSIS Catalog;
It is crystal clear why these two tables created and what they are doing based on their name. the first table controls the AlwaysOn feature of catalog, and the second table is for customized logging levels! (Joost wrote a good blog post about it when it appeared earlier in CTP 2.3). There are also two Views added for this purpose as well;
There are 17 new stored procedures in SSIS Catalog 2016 CTP 3.0. Most of them is to cover changes related to AlwaysOn and Customized Logging Levels, however there are some others. Let’s see.
There are 5 stored procedures for customized logging levels as below;
There are 4 stored procedures for AlwaysOn as below;
There are 6 new stored procedures to handle Single Package Deployment (or Incremental Package Deployment). Yes, in SSIS 2016 you can deploy a single package rather than the whole project, and that is done with help of stored procedures below;
Read Andy Leonard’s blog post to learn more about Incremental Package Deployment feature in SSIS 2016.
And finally there are two new stored procedures for objects;
You might think what is Object here? well, the object in SSIS Catalog can be; Project, Package, Folder, Parameter, and Environment Variable. However the object here (I believe) means only deploy-able object, that can have versions and parameters. in SSIS 2012 and 2014 only Project was a deploy-able object. However with SSIS 2016 you can deploy a single package as well, so then package is an object. So these two new stored procedures are added to handle both project and package as deploy-able objects.