SSIS Catalog Changes in 2016


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;




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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

1 thought on “SSIS Catalog Changes in 2016

Leave a Reply