Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What's New in SQL Server Integration Services 2016 - Part 1


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend these FREE MSSQLTips webcasts >> click to register


Problem

SQL Server 2016 has been released to General Availability (GA) in June 2016. This release comes packed with dozens of new features and capabilities, and Integration Services (SSIS) is no exception. This tip aims to give you an overview of all those changes. Some tips have been written already detailing some of those new features. If applicable, references to earlier tips will be provided. If there were any changes, updates or bug fixes released in SQL Server 2016 previews after the publication of the corresponding tip, these will be mentioned as well.

Solution

Here are a few things that are new in SQL Server 2016 Integration Services.

AlwaysOn Support for SQL Server Integration Services

In SSIS 2016, support for AlwaysOn Availability Groups has been enhanced for the SSISDB database. You can easily deploy the SSISDB database to an availability group, ensuring high availability for your projects, packages, logs and environments. If a failover occurs, one of the secondary nodes becomes the primary node.

To enable AlwaysOn on the SSIS catalog, you need to follow these steps:

  • Install a Windows Failover cluster, install SQL Server 2016 with Integration Services on each node and enable the Always On feature on each instance.
  • Create the SSIS catalog on the primary node.
  • Add the SSISDB to the Availability Group.
  • Right-click the SSIS catalogs node and choose Enable AlwaysOn Support...
AlwaysOn Support for SQL Server Integration Services

For more information: Always On for SSIS Catalog (SSISDB).

There's also a new role ssis_monitor in the SSISDB database for supporting AlwaysOn. It's intended for internal use only.

SQL Server Integration Services Incremental Deployment

In SQL Server 2012 the project deployment model was introduced for Integration Services. In this deployment model, you could only deploy an entire project - along with all of its packages - to the SSIS Catalog. This made maintenance and versioning a bit harder on the SSIS projects. A tight integration with a source control solution, like Team Foundation Server, was necessary to keep track of the changes and to make sure you deployed the correct version of the packages to the Catalog. In SSIS 2016 however, it's possible again to deploy individual packages, which makes deployments a lot easier, especially for bug fixes.

You can find more information in the tip SQL Server Integration Services 2016 Incremental Package Deployment. When the tip was published, SQL Server Data Tools for SQL Server 2016 (aka Visual Studio 2015) was not yet released. The tip only has screenshots when you use the SSIS Deployment Wizard. Now you can also deploy individual packages by selecting them in the Solution Explorer of Visual Studio, right-clicking them and choosing Deploy Package.

SQL Server Integration Services Incremental Deployment

SQL Server Integration Services Always Encrypted support

Always Encrypted is a new feature of the database engine to easily encrypt one or more columns of a table. For an introduction to this feature, check out the tip SQL Server 2016 Always Encrypted. The support by SSIS is described in the following three tips:

SSIS_logreader database role for SSISDB

In the SSIS catalog, a lot of information about the execution of the packages is automatically logged. There are a set of built-in reports helping you to find detailed logging results about those executions. However, in earlier versions you needed to be an SSIS admin in order to view the data inside the reports. If not, the reports would remain empty.

Empty report due to insufficient permissions

When users are added to the new ssis_logreader database role, they can also view the reports with data without being an administrator.

Not-Empty report with permissions in the SSIS_LogReader database role

New SQL Server Integration Services Logging Levels

Inside the SSIS Catalog, a logging level dictates exactly which events and statistics are logged when a package is running. A new logging level has been introduced: RuntimeLineage. There's not much information available about those logging level. It's most likely added to support a future lineage tracking capability.

One of my favorite new features however is the ability to create your own custom logging level. If you're for example interested in errors and warnings only, you can add only the OnError and OnWarning event to a logging level, which makes the logging extremely efficient. You can find more information about custom logging levels and the RuntimeLineage logging level in the tip Integration Services Logging Levels in SQL Server 2016. A new capability was added after the tip was published (much to my delight): the ability to set a custom logging level as the server default. This allows you to drastically minimize the events being logged and thus optimize the performance and size of SSISDB.

New SQL Server Integration Services Logging Levels

Retrieving Error Column Name in the SQL Server Integration Services Data Flow

In SSIS 2016, it's much easier to retrieve the name of the column that caused an error. In previous versions, you had to rely on the LineageID of the column and it was not easy to find out to which column it belonged. Now, a new .NET function has been added - GetIdentificationStringByID - which allows you to easy find the column name in a script component. The tip Retrieve Error Column in SSIS 2016 explains how exactly you can do this. Note that the new function has been renamed! In the original tip it was still called GetIdentificationStringByLineageID, now it has been shortened to GetIdentificationStringByID. Furthermore, a new interface has been added - IDTSComponentMetaData130 - to the API in support of the GetIdentificationStringByID function. This means that you now need two lines of code to retrieve the column name, instead of only one. For example, the following line comes from the original tip:

Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn);

It needs to be replaced with the following two lines of code:

IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

Not only is this new functionality added to the script component, but at several other places as well:

  • The DiagnostEx logging event writes column mapping to the log.
  • In the Advanced Editor, you can see the name of the upstream column in the properties of an input/output column.
  • The Error Column Name is also added to the data viewer on the error output.

SQL Server Integration Services Control Flow Parts

The tip SQL Server Integration Services 2016 Control Flow Templates Introduction showed how you can build a control flow (data flow optionally included) which you could then save as a separate object. It was then possible to insert this object directly on other packages by selecting it from the SSIS Toolbox. This promotes reuse of SSIS functionality, especially for pieces of logic that are always almost exactly the same in every package. In the tip I mentioned that "template" was not an ideal name, as there's little flexibility in changing the object once it was inserted in a package. But the SSIS team has renamed this functionality to control flow parts, which is a much better name as it clearly indicates the purpose of this feature. As a result of this name change, the file extension of a control flow part has changed from .dtst to .dtsp.

SQL Server Integration Services Control Flow Parts

This may cause control flow parts created in a SQL Server 2016 preview to appear under the Miscellaneous node instead of the control flow parts node.

Conclusion

Integration Services 2016 comes packed with a lot of new features and capabilities. In part 1 of this tip, an overview is given of these enhancements. Notable features are the custom logging levels, control flow parts and the ability to deploy individual packages again. In part 2, we'll finish the overview with the remainder of the new enhancements.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools