SSIS 2016; What’s New in CTP 2.3

Posted by on Sep 6, 2015 in SQL Server 2016, SSIS | 4 Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

11

SQL Server 2016 is under a heavy improvement plan, The latest CTP version (Community Test Preview) of it is 2.3 which released as preview three days ago. CTP 2.3 has some new features across all components of SQL Server, such as SSRS, SSAS, SSIS, MDS and etc. Previously I wrote about some new changes in MDS CTP 2.0 and MDS CTP 2.2, I will write a blog post soon about new features in MDS CTP 2.3. There are some 3 main new changes in this version of SQL Server Integration Services (SSIS) which worth going through it. In this blog post I’ll explain new features available in SSIS 2016 CTP 2.3. Changes in nutshell are:

  • Support for OData v4 Protocol through OData Source
  • New Error Column Name in the Data Flow
  • Advanced and Customized Logging Level in SSIS Catalog

If you want to learn more about SSIS and its features I recommend you to read this book, or watch my Channel 9 tutorial video series of SSIS.

Preparation

If you want to try samples on this post or try new features of SSIS 2016 CTP 2.3 yourself, then you have to install prerequisites below:

Version number of SQL Server 2016 CTP 2.3 is: 13.0.500

Management Studio or SSMS won’t be installed with SQL Server, you have to download and install it separately. the latest SSMS at the time of writing this blog post is the one that mentioned in above URL.

In this version of SQL Server fortunately SSDT and Visual Studio combined to come as an integrated version, So need to download SSDT August preview in above or below URL.

0

SSDT Data Tools Preview for Visual Studio 2015

 

OData Source Support for V4 Protocol

SSIS now supports Odata v4 protocol through OData Source. OData is Open Data Protocal for REST services. In very short description 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. You can find OData Source in SSIS Toolbox in Data Flow as below;

1

You have to create a new OData Source, and you need an OData URL connection for that.

2

As an example you can use Northwind sample Odata URL here: http://services.odata.org/V4/Northwind/Northwind.svc/

3

Then the OData Source in SSIS Identifies the data structure based on the protocol and lists all identified data structures in the categories section

4

You can choose one of the categories (for example Employee) and see that feed URL value set automatically

5

You can also preview the data in the category

6

This data can be used in SSIS Data Flow to be loaded in any destination or for applying any data transformation.

 

Error Column Name

If you worked with SSIS before you know that in SSIS Data Flow you get an Error Output in most of the components. The Error Output usually contains the total row that caused error (structure of columns depends on the component) plus an ErrorCode and ErrorColumn values. The ErrorCode is a code which can be used to fetch error description. the way is just use a script transformation component and use script below to fetch error description;

This method is not new, and many blog posts has been written about it. The ErrorColumn however is a code that leads you to the LineageID of the column in SSIS Data Flow. Here is what comes new. Data Lineage ID is a dynamically generated column ID at the time of execution of SSIS Package. Because of dynamic nature of this ID there was no built-in way of fetching the error column name based on the lineage ID provided in ErrorColumn value so far. However there were some third party tools that reads the lineage ID of SSIS package’s column and components can could be used in combination with this ErrorCode to provide the column name. The method however was not built-in method and required additional steps and more work.

SSIS 2016 CTP 2.3

Fortunately in this version of SSIS a built-in function introduced that can be used to fetch error column name! That’s great isn’t it? Here is an example of that;

Setup Error Output in a data flow component

7

Redirect Error output to an Script Transformation (Used as a transformation)

8

Use ErrorCode and ErrorColumn as Input Columns

9

Create two output columns of type string (DT_STR) with enough length

10

Write the script below in the script transformation’s ProcessInputRow method;

And then you will be able to see the actual column name. The result of Script component now can be used in combination with other components for logging or troubleshooting purposes.

 

Advanced and Customized SSIS Catalog Logging

SSIS 2012 came with a new mode of package deployment and logging through a centralized database for metadata and logging information of SSIS packages and projects called SSIS Catalog. SSIS Catalog so far had four logging levels, and those four logging levels were static based on some events and actions logged in each level. Here are four Logging levels : None, Basic, Performance, and Verbose details:

19

The new version of SSIS comes with a customized logging level; You can now use the the Customized Logging Level with right click on SSISDB in Management Studio (you need latest version of SSMS for it)

11

Create a Custom Logging Level with a description

12

You can then choose some statistics that you want to be logged

13

There are three statistics;

  • Executable Execution Statistics

logs execution statistics for all executable objects in package (Containers, Packages, Control Flow Components)

  • Component Data Volume Statistics

logs number of rows passed through data flow components

  • Component Execution Statistics

logs execution statistics for all components in the data flow

You can also choose exact events that you want to be logged (this part is somehow similar to SSIS legacy logging method). You can also choose if you want to capture event’s context or not.

14

After setting up custom logging levels, then you can choose them besides other logging levels at the time of package execution

15

16

If you want to learn more about SSIS Catalog Logging watch this tutorial video.

 

There is also a new Runtime Lineage Logging Level introduced in this version. This level of logging logs dynamic data lineage information

17

 

Besides all additional features in the logging a new role has been added in SSIS for log reader, named ssis_logreader.

18

The SSIS Catalog Database also had some changes to reflect the logging level changes as mentioned above. Later on I’ll write another blog post about detailed changes in SSIS Catalog.

 

 

 

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad

Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.


4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *