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;

alwayson_support_state

customized_logging_levels

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;

catalog.alwayson_replicas

catalog.customized_logging_levels

Stored Procedures

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.