With all of the other new features in SQL Server 2016, it’s easy to overlook the enhancements that Microsoft has made to SQL Server Integration Services (SSIS). First introduced with SQL Server 2005, SSIS replaced the earlier Data Transformation Services (DTS) and is SQL Server’s primary Extraction Transfer Load (ETL) tool. SSIS more than a simple ETL tool. SISS enables database administrators and developers to design and implement complex ETL applications consisting of multiple data sources, joins, merges, complex logic, error handling logging and retries. Some of the main enhancements in SQL Server 2016 SIS include:
- Support for Always On in the SSIS Catalog: With SQL Server 2016, SSIS enables you to deploy to a centralized SSIS Catalog (an SSISDB database). You can provide high availability for the SSISDB by making it part of an Always On Availability Group. This will enable availability for the SSISDB just like a normal user database. In the event of a failover, one of the secondary nodes will automatically become the new primary node.
- Support for Always Encrypted: SSIS in SQL Server 2016 fully supports the new Always Encrypted feature, enabling you to perform ETL tasks with Always Encrypted databases. At this time you need to use the new ADO.NET provider. You can’t use the OLE DB provider.
- New ssis_logreader database-level role: In previous versions of SQL Server, only users in the ssis_admin role were able to access the views that contain logging output. SQL Server 2016 provides a new ssis_logreader database-level role that enables you to grant access to views that contain SSIS logging data to users who aren't administrators.
- New custom logging level: In previous versions of SQL Server, there were four built-in logging levels for SSIS packages: None, Basic, Performance or Verbose. SQL Server 2016 adds the new RuntimeLineage logging that collects the data required to track lineage information in the data flow. You can also create and save multiple customized logging levels and select the logging level to use each time you run a package.
- Column names for errors in the data flow: When you redirect rows in the data flow that contain errors to an error output, the output contains a numeric identifier for the column that contained the error. However, it does not display the name of the column. With SQL Server 2016 you now have the ability to display the name of the column that generated the error.
- Support for OData v4 data sources: The SQL Server 2016 SSIS OData data source and the OData Connection Manager now support the OData v3 and v4 protocols. OData v3 support includes the ATOM and JSON data formats. OData v4 support includes the JSON data format.
- Support for the Hadoop file system (HDFS): SQL Server 2016 SSIS data sources include support for HDFS. SSIS 2016 provides connection managers for Hadoop clusters and tasks to perform common HDFS operations.
- Azure Feature Pack for SSIS: The Azure Feature Pack for SSIS 2016 contains connection managers to connect to Azure data sources and tasks to perform common Azure operations.
- Support for Built-in R Services: SSIS 2016 supports extracting data and loading the output of R analysis, as well as building, running and retraining R models.
- Incremental package deployment: The Incremental Package Deployment feature lets you deploy one or more packages to an SSIS project without the need to deploy the whole project.
- SSISDB Upgrade Wizard: You can use the new SSISDB Upgrade Wizard to upgrade the SSIS Catalog database from a prior version.
In addition to these features, there are many other enhancements that are a part of SSIS 2016. For more information you can check out What's New in Integration Services.
HPE and Microsoft are the underwriters of this article.