SSIS Catalog Changes in 2016

Posted by on Dec 4, 2015 in SSIS, SSIS Catalog | One Comment
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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.

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *