SSIS Catalog Changes in 2016

2

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;

1

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;

2

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;

set_customized_logging_level_value
set_customized_logging_level_description
rename_customized_logging_level
delete_customized_logging_level
create_customized_logging_level

3

There are 4 stored procedures for AlwaysOn as below;

update_replica_info
refresh_replica_status
delete_replica_info
add_replica_info

4

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;

update_package_deployment_status
prepare_packages_deploy
get_updatedpackages
deploy_packages
create_deploy_package_operation
clean_update_packages

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;

update_project_object
update_object_versions

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

1 thought on “SSIS Catalog Changes in 2016

Leave a Reply