TCS ENTSOL BI

SSIS SQL Server 2016 & 2017

What’s New overview

Boinon Stéphane
March 2018
 SSIS SQL SERVER 2016&2017 What’s new chapters

• 01 SSISSQL Server 2016 Manageability

• 02 SSISSQL Server 2016 Connectivity

• 03 SSIS SQL Server 2016 Usability and Productivity

• 04 SSIS SQL Server 2016 Master Data Services module

• 05 SSIS SQL SERVER 2016 Self Service Labs

• 06 SSIS SQL Server 2017 What’s new

• 07 SSIS SQL Server in Azure with Azure Data Factory

2
 SSIS SQL SERVER 2016 What’s new

Manageability Connectivity Usability & Productivity

Incremental Project Deployment Azure storage connectors Multi-Version Support in SSDT
Incrementally deploy projects to the Move data to/from Azure storage for Ability to author, execute, deploy and
SSIS Catalog. hybrid data scenarios. debug multiple versions of SSIS
packages from a single version of SSDT.
Error Column Name Support HDInsight Tasks
See lineage identification string from Orchestrate HDInsight jobs and manage Designer Improvement
the log / data viewer your HDInsight cluster life cycle directly A number of enhancement and fixes on
from SSIS. the designer capabilities such as drag
Custom Logging Level and drop, resizing, …
Customize your own logging level for HDFS Connectors
more flexibility Mover data to/from Windows File system Unified Install for SSDT
and HDFS Faster and easier to install
Package Template
Achieve code usability by creating Hadoop tasks Catalog View Perf Improvement
template part Orchestrate HIVE jobs and manage the Make viewing the SSIS catalog view
lifecycle of your on-premises Hadoop from faster
Always On Support the SSIS Control Flow.
Build your SSIS server farm with high
availability Expanded Connectivity
OData V4, Excel 2013/2016,
Automatic MaxBufferSize Teradata/Oracle
Automatically calculate the suitable
max buffer size for you
Execute SQL Task to run analytics with R
Prepare data for model training and trigger 3
R prediction
01 SSIS SQL Server 2016 Manageability

4
 01 SSIS SQL Server 2016 Manageability

Package incremental deployment is occurring since SQL Server 2016 CTP 2.3
Deploy one or more packages in a project (new or existing) without having to deploy the
entire project can be done via: • Integration Services Deployment Wizard,
• SQL Server Management Studio (Wizard), • SQL Server Data Tools (Wizard) • Stored
5
procedure ([catalog]. [deploy_packages]) • Management Object Model (MOM) API.
 01 SSIS SQL Server 2016 Manageability

New roles in SSISDB is occurring since SQL Server 2016 CTP 2.3
• SSIS_Monitor: This role is used for AlwaysOn and is used by the SQL Server Agent and
should not be used by a user
• SSIS_LogReader : Allows a user to read reports in the Catalog and avoid giving
6
SSIS_admin role to see the reports
 01 SSIS SQL Server 2016 Manageability

No more error output results provided only through a numeric identifier.

Since SQL server 2016 CTP 2.3 Get the name of the column in case of error via
- Data Viewer which now displays a description of the error and the name of the offending column,
-The DiagnosticEx log event,
7
- Any Script component using GetIdentificationStringByLineage method
 01 SSIS SQL Server 2016 Manageability

New level of custom logging in the SSIS Catalog since SQL Server CTP 3.0
Since SSIS 2012 version, SSIS Catalog db has been introduced with 4 logging levels (None, Basic,
Performance, Verbose). With customized logging level that is done via Catalog db, you can now
choose some of the three statistics and exact events that you want to be logged. After setting up
custom logging levels, then you can choose them besides other logging levels at the time of 8
package execution. There is also a new Runtime Lineage Logging Level introduced in this version
 01 SSIS SQL Server 2016 Manageability

Package Template is occurring since SQL Server 2016 CTP 3.0

Save a task or a container in a template (.dtsxt file) and reuse this template in one
or more packages. Pros: Save time when designing a package and its maintenance.
Cons: Template limited to a single task or a single container (not as efficient and 9
complete as mapplet/worklet reusable components present within Informatica ETL tools)
 01 SSIS SQL Server 2016 Manageability

Always On support for SSIS db catalog

For more technical details on AlwaysOn support for SSISDB, review following article
For detailed explanations on SSIS catalog review following article
10
 01 SSIS SQL Server 2016 Manageability

SSIS calculates the optimal buffer size to avoid creating disk buffers (I / O) when the initially
specified buffer size exceeds the amount of available RAM
Improving data flow performance with SSIS AutoAdjustBufferSize property article
Another article explaining how SQL Server 2016 SSIS Data Flow Buffer Auto Sizing
capability benefits data loading on Clustered Columnstore tables. 11
02 SSIS SQL Server 2016 Connectivity

12
 02 SSIS SQL Server 2016 Connectivity

Since SQL Server CTP 3.0 Hadoop and HDFS are supported in Integration Services
New components to recover Hadoop feeds and work on HDFS files from new data
sources. This is part of the Azure Feature Pack and the elements allow you to do this are:
-Connection manager: Hadoop Connection Manager
-Control flow Tasks: Hadoop File System Task, Hadoop Task Hive Task, Hadoop Pig Task
13
-Data flow components: HDFS File Source and HDFS File Destination
 02 SSIS SQL Server 2016 Connectivity

Azure Feature Pack for SSIS is an extension that provides SSIS components to connect
and work with Azure. It allows Data transfer between Azure and Processing of data
stored in Azure. It includes :
Connection manager : Azure Storage and Azure Subscription Connection Managers
Control Flow Tasks: Azure Blob Upload, Azure Blob Download, Azure HDInsight Hive,
Azure HDInsight Pig, Azure HDInsight Create Cluster, Azure HDInsight Delete Cluster 14
tasks Data flow components: Azure Blob Source & Azure Blob Destination components
02 SSIS SQL Server 2016 Connectivity

15
 02 SSIS SQL Server 2016 Connectivity

Possibility in any Foreach Loop editor to browse Azure blob containers or HDFS nodes.
Ability to retrieve partitioned data from a blob storage via a job map reduce
Ability to run map reduce jobs in parallel across multiple nodes / clusters at once
Ability to create clusters on demand and destroy them at the end of transformation jobs
16
 02 SSIS SQL Server 2016 Connectivity

SQL Server 2016 CTP 2.3 supports OData V4 Protocol

SSIS now supports Odata v4 protocol through OData Source. OData is Open Data
Protocol for REST services. OData provides protocol for REST services that can be used
via applications. OData Source in SSIS can read the protocol from an OData Source
Connection. OData v4 support includes the JSON data format. 17
 03 SSIS SQL Server 2016 Usability and Productivity

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
Improved performance for SSIS Catalog views : most SSIS catalog views now perform
better when they're run by a user who is not a member of the ssis_admin role.
SSIS supports the built-in R services in SQL Server. You can use SSIS not only to extract
18
data and load the output of analysis, but to build, run and periodically retrain R models
 03 SSIS SQL Server 2016 Usability and Productivity

SSIS Project backward compatibility is possible by using Visual Studio 2015 SSDT release
The default target version is SQL Server 2016. SQL Server 2014 and SQL Server 2012 are
supported as well, while older versions (2005 and 2008) are not.
If you have older projects than SQL Server 2012, you will need to upgrade them first
before you can use the latest SSDT version. 19
 03 SSIS SQL Server 2016 Usability and Productivity

Balanced Data Distributor data flow task component is now natively integrated into SSIS
module installation
Improving data flow task performance with SSIS Balanced Data Distributor property
article
Another article explaining how to parallelize any Data Flows loading with SSIS Balanced
20

Data Distributor Transformation .

 04 SSIS SQL Server 2016 Master Data Services module
Modeling and
Performance and Scale Security and Admin
Management
Excel add-in Transaction log retention Granular security permissions
4-8x perf increase for SQL Server 2012 Configurable settings for retaining the Allows permissions to be set around read,
and SQL Server 2014. Up to 15x in SQL MDS transaction history table to enable write, create, and delete.
2016. automatic truncation.
Multiple administrator roles
Entity based staging Entity sync relationship
Support for Super User and Model
Loading data into MDS is now 15-200% Share entities and data between models. Admin roles allows for multiple system
faster than SQL Server 2014.
Many to many relationships administrators, and model level admins.
Model deployment Smarter caching of permissions
Create derived hierarchies that support
Faster deployment, and removed size many-to-many relationships. Reduces the overhead of adding member
limitations when deploying models
Attribute filters and attribute level security settings.
with data.
Member revision history
Custom indexes Filter domain based attribute values
based on another attribute. Track change history of members (SCD
Create non-clustered indexes on one or
Business rule extensions type 2) through specialized subscription
more attributes to improve query view.
performance. Apply user defined SQL scripts as
business rule conditions and actions. Change sets and approval
Use change sets to save pending changes.

21
 04 SSIS SQL Server 2016 Master Data Services module

Master Data Services is a service of SQL Server first released in SQL Server 2008R2, and
enhanced in SQL Server 2012. There was no changes in MDS of SQL Server 2014.

All new features and remaining features of MDS SQL Server 2016 are described in this
article
22
 04 SSIS SQL Server 2016 Master Data Services module

Master Data Services is a service of SQL Server first released in SQL Server 2008R2, and
enhanced in SQL Server 2012. There was no changes in MDS of SQL Server 2014.

Other articles about MDS SQL server versions are described in these articles :
http://radacad.com/mds-2016-whats-new-in-ctp-2-2 23
http://radacad.com/change-set-and-approval-workflow-in-mds-2016
04 SSIS SQL Server 2016 Master Data Services module

24
04 SSIS SQL Server 2016 Master Data Services module

25
04 SSIS SQL Server 2016 Master Data Services module

26
 05 SSIS SQL SERVER 2016 Self Service Labs
Exploring What's New in SQL Server 2016 Integration Services (Duration 2h)
In this lab you will explore the new features delivered with SQL Server 2016 Integration
Services. This will involve capturing and logging complete error information, working
with control flow parts, incrementally deploying an individual package, configuring
customized logging levels, and using the Azure Feature Pack to enable the design of
Integration Services packages that connect to, and manage, Azure resources and
services.
Developing a SQL Server 2016 Master Data Services Solution (Duration 2h)
In this lab you will develop a SQL Server 2016 Master Data Services solution to store and
manage geographic master data. This will involve using two tools to create and manage
the solution. You will first use the Master Data Services Add-in for Excel to create entities,
and to also insert new members. You will then use the Master Data Manager Web
application to explore, review and maintain members, and also to create a business rule
and a derived hierarchy. Finally, you will create several subscription views that will be
used by a pre-developed SQL Server 2016 Integration Services solution to populate a
dimension table
Exploring What's New in SQL Server 2016 Master Data Services (Duration 2h)
In this lab you will explore the new features delivered with SQL Server 2016 Master Data
Services. This will involve exploring security and entity improvements for performance
and history management, using the new user interface to define business rule
extensions, resolving merge conflicts, managing approval workflows with change sets,
configuring a derived hierarchy that traverses a many-to-many relationship, replicating
27
entities between models with Entity Sync, and managing deleted members.
 06 SSIS SQL Server 2017 What’s new

All new features present within SQL Server 2017 are described in this article
Change Data Capture, Master Data Services and Data Quality Services modules are not
yet supported on Linux version.
28
 06 SSIS SQL Server 2017 What’s new
SSIS Scale Out is available in SQL Server vNext aka 2017 version CTP1
SQL Server Integration Services (SSIS) Scale Out provides execution of SSIS packages by
distributing package executions across multiple computers. After you set up Scale Out,
you can run multiple package executions in parallel, in scale-out mode, from SSMS.

You can set up Scale Out in the following configurations:

• On a single computer, where a Scale Out Master and a Scale Out Worker run side by
side on the same computer.
• On multiple computers, where each Scale Out Worker is on a different computer.
Detailed features on Scale Out mode are described in article1 and article 2
A new
configuration,
called “Retry
count” is
available for a
package
executing in
Scale Out. It
specifies the
retry count if a
package 29
execution fails.
 07 SSIS SQL Server in Azure with Azure Data Factory
Lift& shift any SSIS Packages to the Cloud on Azure Data Factory V2

Azure Data Factory V2 has the capability for executing SSIS packages on Azure.
SSIS packages can be moved to the cloud using a dedicated SSIS “Integration Runtime” (IR) called
Azure-SSIS R, which is a managed hosting environment, available with ADF V2.
Get started by visiting this step-by-step instructional tutorial on how to leverage Azure Data Factory
to provision any SSIS packages to be deployed, managed and monitored in the cloud.
For more information on how to deploy and use it, read Executing SSIS Packages on Azure overview.
Complete overview for deploying SSIS packages with Azure Data Factory is described in this article
Also, read this article, to learn about the SSIS Integration Runtime (IR) concept on ADF V2
and this article to understand the SSIS on ADF V2 Pricing an provisioning 30