WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 520 reviews for our classroom and online training
A summary of the main new features in SQL Server Integration Services 2016
My favourite new feature in SSIS 2016 is the ability to group components of a package together for reuse elsewhere, but there are quite a few more nice new ideas!

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:

Different icons

The only thing which has changed is the task icons.


Control flow task icons have also changed:

Control flow icons

Bye bye colourful icons ...


There's a new way to show the SSIS toolbox too:

SSIS toolbox icon

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:

New Hadoop tasks

The control flow tasks to do with Hadoop.


There's also a new loop type for Hadoop files:

HSFD file enumerator

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:

New data flow tasks

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:

Adding a control flow 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:

Package part design

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:

Using a package part

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:

Added package part

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:

Excel sources

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:

New feature Notes
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.

This blog has 0 threads Add post