BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This is part of a wide-ranging blog explaining the new features in every part of SQL Server 2016.
Posted by Andy Brown on 08 July 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
What's new in Integrated Services 2016 (SSIS 2016)?
Not much is the short answer, at least if you're in the business of creating packages. This blog gives a quick run-down of what's changed in SSIS 2016 for package builders.
I've included a summary of the main changes for SSIS administrators at the bottom of this blog.
A slightly different look and feel
Microsoft seem to be on a mission to drain the colour out of their software. Here's what a typical data flow task in SSIS 2016 looks like:
The only thing which has changed is the task icons.
Control flow task icons have also changed:
Bye bye colourful icons ...
There's a new way to show the SSIS toolbox too:
You'll find this next to the Variables tool.
What this all shows is that not much has changed!
New control flow tasks
The main (possibly the only) new control flow tasks are to do with Hadoop, a file system for storing large amounts of data:
The control flow tasks to do with Hadoop.
There's also a new loop type for Hadoop files:
Disappointingly, however, the Foreach loop task hasn't been simplified.
New data flow tasks
In the data flow toolbox, these are the new tasks I don't recognise:
The OData Source task now supports version 4. The Balanced Data Distributor task takes a single input and uses multithreading to distribute the incoming rows uniformly between multiple outputs (you'd use it to speed up packages).The Data Streaming Destination is a SQL Server Integration Services (SSIS) destination component that lets the OLE DB Provider for SSIS consume output of an SSIS package as a tabular result set.
Note that although Microsoft were intending to include Power Query as an integral part of SSIS 2016, this didn't make it to the release version.
Reusable package parts
You can now combine a group of control flow tasks together for reuse as a package part:
Right-click in Solution Explorer to add a new package part.
You can then add any control and data flow tasks to the package part:
Notice that Visual Studio gives this a file extension of dtsxp. A package part can contain one task only, so here I've had to use a sequence container to contain my tasks.
You can then drag the reusable package part onto any package:
You can click and drag a package part onto any package, as shown here.
Visual Studio shows that you can't edit this package part in situ with a P at the top right corner:
You can't double-click on the package part to edit it, since it's contained in a separate file.
Explicit support for Excel 2013 and 2016
When you create an Excel source, here's what you see:
You can choose the exact version of Excel from which to import (or to which to export, if you're creating a destination).
What's new for SQL Server Administrators?
Lots! Here's s quick summary:
|Compatibility||For the first time, you can use older versions of SSIS packages inside the latest version.|
|Deployment||Deployment has been greatly improved (for example, you can now use incremental deployment and deploy individual packages).|
|Debugging||To my mind SSIS already contained more logging features than I could ever want to use, but these have been further expanded. It's also possible now to see the input and output columns for any data flow task.|
|Cloud connectivity||SSIS 2016 includes the Azure Feature Pack (connection managers and common tasks to use when working with Azure).|
|SSAS Tabular support||SSIS processing tasks now support Analysis Services tabular models. 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.|
I don't claim that this is a comprehensive list, but it is intended as a summary of the features most users will find most interesting.