What's New in Integration Services in SQL Server 2016
Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory
This topic describes the features that have been added or updated in SQL Server 2016 Integration Services. It also includes features added or updated in the Azure Feature Pack for Integration Services (SSIS) during the SQL Server 2016 time frame.
With the public preview of Azure Data Factory version 2 in September 2017, you can now do the following things:
- Deploy packages to the SSIS Catalog database (SSISDB) on Azure SQL Database.
- Run packages deployed to Azure on the Azure-SSIS Integration Runtime, a component of Azure Data Factory version 2.
For more info, see Lift and shift SQL Server Integration Services workloads to the cloud.
These new capabilities require SQL Server Data Tools (SSDT) version 17.2 or later, but do not require SQL Server 2017 or SQL Server 2016. When you deploy packages to Azure, the Package Deployment Wizard always upgrades the packages to the latest package format.
Manageability
Better deployment
Better debugging
Better package management
Connectivity
Expanded connectivity on premises
Expanded connectivity to the cloud
Azure Storage connectors and Hive and Pig tasks for HDInsight - Azure Feature Pack for SSIS released for SQL Server 2016
Support for Microsoft Dynamics online resources released in Service Pack 1
Usability and productivity
Better install experience
Better design experience
SSIS Designer creates and maintains packages for SQL Server 2016, 2014, or 2012
Multiple designer improvements and bug fixes.
Better management experience in SQL Server Management Studio
Other enhancements
Run the SSISDB Upgrade Wizard to upgrade the SSIS Catalog database, SSISDB, when the database is older than the current version of the SQL Server instance. This occurs when one of the following conditions is true.
You restored the database from an older version of SQL Server.
You did not remove the database from an Always On Availability Group before upgrading the SQL Server instance. This prevents the automatic upgrade of the database. For more info, see Upgrading SSISDB in an availability group.
For more info, see SSIS Catalog (SSISDB).
The Always On Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. An availability group supports a failover environment for a discrete set of user databases known as availability databases that fail over together. For more information, see Always On Availability Groups.
In SQL Server 2016, SSIS introduces new capabilities that let you easily deploy to a centralized SSIS Catalog (i.e. SSISDB user database). In order to provide high availability for the SSISDB database and its contents - projects, packages, execution logs, and so on - you can add the SSISDB database to an Always On Availability Group, just like any other user database. When a failover occurs, one of the secondary nodes automatically becomes the new primary node.
For a detailed overview and step-by-step instructions for enabling Always On for SSISDB, see SSIS Catalog.
The Incremental Package Deployment feature lets you deploy one or more packages to an existing or new project without deploying the whole project. You can incrementally deploy packages by using the following tools.
Deployment Wizard
SQL Server Management Studio (which uses the Deployment Wizard)
SQL Server Data Tools (Visual Studio) (which also uses the Deployment Wizard)
Stored procedures
The Management Object Model (MOM) API
For more info, see Deploy Integration Services (SSIS) Projects and Packages.
SSIS already supports the Always Encrypted feature in SQL Server. For more info, see the following blog posts.
In previous versions of the SSIS catalog, only users in the ssis_admin role can access the views that contain logging output. There is now a new ssis_logreader database-level role that you can use to grant permissions to access the views that contain logging output to users who aren't administrators.
There is also a new ssis_monitor role. This role supports Always On and is for internal use only by the SSIS catalog.
The new RuntimeLineage logging level in the SSIS catalog collects the data required to track lineage information in the data flow. You can parse this lineage information to map the lineage relationship between tasks. ISVs and developers can build custom lineage mapping tools with this information.
Previous versions of the SSIS catalog let you choose from four built-in logging levels when you run a package: None, Basic, Performance, or Verbose. SQL Server 2016 adds the RuntimeLineage logging level. In addition, you can now create and save multiple customized logging levels in the SSIS catalog, and pick the logging level to use every time you run a package. For each customized logging level, select only the statistics and events you want to capture. Optionally include the event context to see variable values, connection strings, and task properties. For more info, see Enable Logging for Package Execution on the SSIS Server.
When you redirect rows in the data flow that contain errors to an error output, the output contains a numeric identifier for the column in which the error occurred, but does not display the name of the column. There are now several ways to find or display the name of the column in which the error occurred.
When you configure logging, select the DiagnosticEx event for logging. This event writes a data flow column map to the log. You can then look up the column name in this column map by using the column identifier captured by an error output. For more info, see Error Handling in Data.
In the Advanced Editor, you can see the column name for the upstream column when you view the properties of an input or output column of a data flow component.
To see the names of the columns in which the error occurred, attach a Data Viewer to an error output. The Data Viewer now displays both the description of the error and the name of the column in which the error occurred.
In the Script Component or a custom data flow component, call the new GetIdentificationStringByID method of the IDTSComponentMetadata100 interface.
For more info about this improvement, see the following blog post by SSIS developer Bo Fan: Error Column Improvements for SSIS Data Flow.
Note
(This support has been expanded in subsequent releases. For more info, see Expanded support for error column names and New IDTSComponentMetaData130 interface in the API.)
The DiagnosticEx event now logs column information for all input and output columns, not just lineage columns. As a result we now call the output a pipeline column map instead of a pipeline lineage map.
The method GetIdentificationStringByLineageID has been renamed to GetIdentificationStringByID. For more info, see Column names for errors in the data flow.
For more info about this change and about the error column improvement, see the following updated blog post. Error Column Improvements for SSIS Data Flow (Updated for CTP3.3)
Note
(In RC 0, this method has been moved to the new IDTSComponentMetaData130 interface. For more info, see New IDTSComponentMetaData130 interface in the API.)
In SQL Server Server Properties, under the Server logging level property, you can now select a default server-wide logging level. You can pick from one of the built-in logging levels - basic, none, verbose, performance, or runtime lineage - or you can pick an existing customized logging level. The selected logging level applies to all packages deployed to the SSIS Catalog. It also applies by default to a SQL Agent job step that runs an SSIS package.
The new IDTSComponentMetaData130 interface adds new functionality in SQL Server 2016 to the existing IDTSComponentMetaData100 interface, especially the GetIdentificationStringByID method. (The GetIdentificationStringByID method is moved to the new interface from the IDTSComponentMetaData100 interface.)There are also new IDTSInputColumn130 and IDTSOutputColumn130 interfaces, both of which provide the LineageIdentificationString property. For more info, see Column names for errors in the data flow.
When you upgrade SSIS projects from previous versions to the current version, the project-level connection managers continue to work as expected and the package layout and annotations are retained.
When you set the value of the new AutoAdjustBufferSize property to true, the data flow engine automatically calculates the buffer size for the data flow. For more info, see Data Flow Performance Features.
Save a commonly used control flow task or container to a standalone template file and reuse it multiple times in one or more packages in a project by using control flow templates. This reusability makes SSIS packages easier to design and maintain. For more info, see Reuse Control Flow across Packages by Using Control Flow Package Parts.
The new reusable control flow templates released in CTP 3.0 have been renamed as control flow parts or package parts. For more info about this feature, see Reuse Control Flow across Packages by Using Control Flow Package Parts.
The OData Source and the OData Connection Manager now support the OData v3 and v4 protocols.
For OData V3 protocol, the component supports the ATOM and JSON data formats .
For OData V4 protocol, the component supports the JSON data format .
For more info, see OData Source.
The Excel Connection Manager, the Excel Source and the Excel Destination, and the SQL Server Import and Export Wizard now provide explicit support for Excel 2013 data sources.
Support for HDFS contains connection managers to connect to Hadoop clusters and tasks to do common HDFS operations. For more info, see Hadoop and HDFS Support in Integration Services (SSIS).
The Hadoop Connection Manager now supports both Basic and Kerberos authentication. For more info, see Hadoop Connection Manager.
The HDFS File Source and the HDFS File Destination how support both Text and Avro format. For more info, see HDFS File Source and HDFS File Destination.
The Hadoop File System task now supports the CopyWithinHadoop option in addition to the CopyToHadoop and the CopyFromHadoop options. For more info, see Hadoop File System Task.
The HDFS File Destination now supports the ORC file format in addition to Text and Avro. (The HDFS File Source supports only Text and Avro.) For more info about this component, see HDFS File Destination.
The ODBC Source and Destination components have been updated to provide full compatibility with SQL Server 2016. There is no new functionality and there are no changes in behavior.
The Excel Connection Manager, the Excel Source, and the Excel Destination now provide explicit support for Excel 2016 data sources.
The Microsoft Connector for SAP BW for Microsoft SQL Server 2016 has been released as part of the SQL Server 2016 Feature Pack. To download components of the Feature Pack, see Microsoft SQL Server 2016 Feature Pack.
The Microsoft Connectors v4.0 for Oracle and Teradata have been released. To download the connectors, see Microsoft Connectors v4.0 for Oracle and Teradata.
The destination adapters for loading data into PDW with AU5 have been released. To download the adapters, see Analytics Platform System Appliance Update 5 Documentation and Client Tools.
The Azure Feature Pack for Integration Services has been released for SQL Server 2016. The feature pack contains connection managers to connect to Azure data sources and tasks to do common Azure operations. For more info, see Azure Feature Pack for Integration Services (SSIS).
With SQL Server 2016 Service Pack 1 installed, the OData Source and OData Connection Manager now support connecting to the OData feeds of Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online.
The latest version of the Azure Feature Pack includes a connection manager, source, and destination to move data to and from Azure Data Lake Store. For more info, see Azure Feature Pack for Integration Services (SSIS)
The latest version of the Azure Feature Pack includes the Azure SQL DW Upload task for populating Azure Synapse Analytics with data. For more info, see Azure Feature Pack for Integration Services (SSIS)
If the SSIS catalog database (SSISDB) belongs to an Always On Availability Group, you have to remove SSISDB from the availability group, upgrade SQL Server, then add SSISDB back to the availability group. For more info, see Upgrading SSISDB in an availability group.
You can now use SSIS Designer in SQL Server Data Tools (SSDT) for Visual Studio 2015 to create, maintain, and run packages that target SQL Server 2016, SQL Server 2014, or SQL Server 2012. To get SSDT, see Download Latest SQL Server Data Tools.
In Solution Explorer, right-click on an Integration Services project and select Properties to open the property pages for the project. On the General tab of Configuration Properties, select the TargetServerVersion property, and then choose SQL Server 2016, SQL Server 2014, or SQL Server 2012.
Important
If you develop custom extensions for SSIS, see Support multi-targeting in your custom components and Getting your SSIS custom extensions to be supported by the multi-version support of SSDT 2015 for SQL Server 2016.
Most SSIS catalog views now perform better when they're run by a user who is not a member of the ssis_admin role.
The Balanced Data Distributor transformation, which required a separate download in previous versions of SQL Server, is now installed when you install Integration Services. For more info, see Balanced Data Distributor Transformation.
The Data Feed Publishing Components, which required a separate download in previous versions of SQL Server, are now installed when you install Integration Services. For more info, see Data Streaming Destination.
The SQL Server Import and Export Wizard can now import data from, and save data to, Azure Blob Storage. For more info, see Choose a Data Source (SQL Server Import and Export Wizard) and Choose a Destination (SQL Server Import and Export Wizard).
The Microsoft Change Data Capture Designer and Service for Oracle by Attunity for Microsoft SQL Server 2016 have been released as part of the SQL Server 2016 Feature Pack. These components now support Oracle 12c in classic installation. (Multitenant installation is not supported) To download components of the Feature Pack, see Microsoft SQL Server 2016 Feature Pack.
The CDC (Change Data Capture) Control Task, Source, and Splitter Transformation components have been updated to provide full compatibility with SQL Server 2016. There is no new functionality and there are no changes in behavior.
The Analysis Services Execute DDL Task has been updated to accept Tabular Model Scripting Language commands.
You can now use all the SSIS task and destinations that support SQL Server Analysis Services (SSAS) with SQL Server 2016 tabular models. The SSIS tasks have been updated to represent tabular objects instead of multidimensional objects. For example, when you select objects to process, the Analysis Services Processing Task automatically detects a Tabular model and displays a list of Tabular objects instead of showing measure groups and dimensions. The Partition Processing Destination now also shows tabular objects and supports pushing data into a partition.
The Dimension Processing Destination does not work for Tabular models with the SQL 2016 compatibility level. The Analysis Services Processing Task and the Partition Processing Destination are all you need for tabular processing.
SSIS already supports the built-in R services in SQL Server. You can use SSIS not only to extract data and load the output of analysis, but to build, run and periodically retrain R models. For more info, see the following log post. Operationalize your machine learning project using SQL Server 2016 SSIS and R Services.
Validate XML documents and get rich error output by enabling the ValidationDetails property of the XML Task. Before the ValidationDetails property was available, XML validation by the XML Task returned only a true or false result, with no information about errors or their locations. Now, when you set ValidationDetails to true, the output file contains detailed information about every error including the line number and the position. You can use this information to understand, locate, and fix errors in XML documents. For more info, see Validate XML with the XML Task.
SSIS introduced the ValidationDetails property in SQL Server 2012 (11.x) Service Pack 2. This new property was not announced or documented at that time. The ValidationDetails property is also available in SQL Server 2014 (12.x) and in SQL Server 2016 (13.x).
- Ideas for SQL: Have suggestions for improving SQL Server?
- Microsoft Q & A (SQL Server)
- DBA Stack Exchange (tag sql-server): Ask SQL Server questions
- Stack Overflow (tag sql-server): Answers to SQL development questions
- Reddit: General discussion about SQL Server
- Microsoft SQL Server License Terms and Information
- Support options for business users
- Additional SQL Server help and feedback
Did you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.
For more information, see How to contribute to SQL Server documentation