By: Koen Verbeeck | Comments | Related: > SQL Server 2016
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...
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 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:
- Reading Always Encrypted Data with SQL Server Integration Services 2016
- Writing Always Encrypted Data with Integration Services 2016
- SQL Server Integration Services 2016 Lookups With Always Encrypted Data
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.
When users are added to the new ssis_logreader database role, they can also view the reports with data without being an administrator.
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.
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.
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
- Earlier tips about the new features discussed in this tip:
- For more SQL Server 2016 tips, you can use this overview.
- The official documentation: What's New in Integration Services.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
