By: Koen Verbeeck | Comments (1) | Related: > SQL Server 2016
Problem
SQL Server 2016 has been released to General Availability (GA) at the first of June 2016. This release comes packed with dozens of new features and capabilities, and Integration Services (SSIS) is no exception. In the first part of this tip series, some new features have already been discussed, such as control flow parts, custom logging levels and incremental deployment. However, the overview is not finished yet. The second part will detail even more new enhancements and capabilities of SSIS 2016.
Solution
AutoAdjustBufferSize in the SSIS Data Flow
The data flow has a new property: AutoAdjustBufferSize. Previously, the sizing of the data flow buffers had to be done manually through the DefaultBufferMaxBufferRows and DefaultBufferSize properties. Both properties have undersized defaults for today's powerful servers (10,000 rows and 10MB respectively). In order to get good performance of the data flow, you always had to tweak those parameters. Those days are now over. You just set AutoAdjustBufferSize to True - the default is regrettably set to False - and you let the data flow itself handle the performance.
You can read more about this property in the tip Improving data flow performance with SSIS AutoAdjustBufferSize property. Also note that this property helps with loading data into a clustered columnstore index, as described in the MSDN blog post SQL Server 2016 SSIS Data Flow Buffer Auto Sizing capability benefits data loading on Clustered Columnstore tables.
Backwards Compatibility in SQL Server Data Tools
A feature that has long been awaited is the ability to develop SSIS packages for multiple versions of SSIS using one single installation of the SQL Server Data Tools (SSDT). Previously, each release of SQL Server came with its own Visual Studio shell. If you had multiple SSIS versions in your environment, it meant you had to install multiple versions of SSDT. Now you need to install only the latest version of SSDT (Visual Studio 2015). In the project properties, you can choose for which version of SSIS you are developing: 2012, 2014 or 2016.
For more information: Backwards Compatibility in SQL Server Data Tools for Integration Services. Furthermore, with this release of SSDT, the database development tools are also included, so is one single product to manage both your database projects and your business intelligence projects (SSIS, SSAS and SSRS).
More Connectivity Options in Integration Services 2016
In Integration Services 2016, the number of supported connections has greatly increased and some existing ones have been improved.
- Support for OData v4 sources. The OData source and the OData connection manager now support the v3 and v4 protocols. v3 supports Atom and JSON, v4 supports only JSON. For an introduction to the OData components, read the tip Using the New OData Source in SQL Server Integration Services.
- Excel 2013 and 2016 support. You can now create an Excel connection to Excel 2013 or Excel 2016. However, it's actually not easy to use those. There's no release of an updated ACE OLE DB Provider, so in order to work with either version, you need to download the entire Access Runtime Library. It's quite some overhead for some small functionality. Also, since Visual Studio is 32-bit only, you need to install the 32-bit version of the library. If you have one single Office component on your machine that is 64-bit, you cannot install the library. Only if everything is 32-bit, you can install it, but the package has to keep running in 32-bit mode. Furthermore, the two new connection types don't bring anything new. You can perfectly read Excel 2013/2016 .xlsx files using the Excel 2010 connection manager.
- Support for Hadoop and HDFS. Connection managers are now
included to connect to Hadoop clusters and tasks are provided to do common HDFS
operations. These are:
- Hadoop File System Task. Comparable with a regular File System Task, but on Hadoop. Supported operations are CopyToHDFS, CopyFromHDFS and CopyWithinHDFS.
- Hadoop Pig Task. Comparable with an Execute SQL Task.
- Hadoop Hive Task. Comparable with an Execute SQL Task.
- HDFS File Source/Destination. They support both Text and Avro format. The destination also supports ORC.
- Updated ODBC components. The ODBC Source/Destination are fully compatible with SQL Server 2016. There are however no changes in functionality or behavior.
- Connector for SAP BW. As part of the SQL Server 2016 feature pack, the connector for SAP BW has been released. This is for SAP NetWeaver BW version 7.0.
- Azure Feature Pack. You can download a set of connection
managers and tasks to do Azure operations, called the
Azure Feature Pack. You can connect to Azure Storage or to an Azure Subscription.
The following tasks are available:
- Azure Blob Download/Upload Task
- Azure HDInsight Create/Delete Task
- Azure HDInsight Hive Task
- Azure HDInsight Pig Task
- Azure Blob Source/Destination
The SQL Server Import/Export now also support Azure Blob Storage as a source or destination. More information on the Azure Feature Pack can be found at Azure Feature Pack for Integration Services (SSIS).
Additional Integration Services 2016 Enhancements
There are several minor enhancements to Integration Services as well.
- Upgrade SSIS Catalog wizard. You can run a wizard to upgrade the SSIS Catalog (and SSISDB) when it has a version older than the current version of SQL Server. This happens when you restored the SSISDB from an older version of SQL Server or when the SSISDB was included in an AlwaysOn Availability Group. In the last case, an automatic upgrade of the SSISDB is prevented. If you want to upgrade the SSISDB, you have to remove it first from the Availability Group. More information about AlwaysOn support can be found in part 1 of this tip series.
- Better upgrade. When upgrading SSIS projects from previous versions, project connection managers should continue to work, and package layout and annotations shouldn't disappear.
- Improved performance for SSIS catalog. This improvement should be most noticeable when the views are run by a user who is not a member of the ssis_admin role.
- New components in the data flow. The balanced data distributor and the data feed publishing components are now available in the data flow. In previous versions you had to download and install them separately.
- Change Data Capture enhancements. The SQL Server 2016 feature pack includes a Change Data Capture Designer and Service for Oracle. This supports Oracle12c in classic installation. The CDC components in SSIS have been updated to support SQL Server 2016, but no new functionality has been added.
- SSAS Tasks are updated for Tabular. All of the SSIS tasks and components now support the SQL Server 2016 Tabular model (which is in JSON instead of XMLA). Only the Dimension Processing Destination is limited to Multidimensional, since you only need the Analysis Services Processing Task and the Partition Processing Destination to process a Tabular model.
- Rich XML validation output in the XML Task. If you enable the ValidationDetails property, you get detailed error information - including line number and position - in the output file. This property is actually already available since SQL Server 2012 service pack 2, but has not been documented or announced so far.
- Multiple designer improvements and bug fixes. There's no documentation available on what exactly has been improved or fixed.
Conclusion
Integration Services 2016 comes packed with a lot of new features and capabilities. In part 1 of this tip series, an overview is given of several enhancements. Part 2 discussed other improvements, such as the AutoAdjustBufferSize property, backwards compatibility and several connectivity enhancements for the cloud and big data.
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
