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:

• Microsoft SQL Server 2016 CTP 2.3

Version number of SQL Server 2016 CTP 2.3 is: 13.0.500

• Latest SQL Server Management Studio

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.

• Latest SQL Server Data Tools

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.

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;

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

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

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

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

You can also preview the data in the category

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;

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    }

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

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

Use ErrorCode and ErrorColumn as Input Columns

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

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

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.ErrorColumnName = ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn);
        Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    }

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:

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)

Create a Custom Logging Level with a description

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

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.

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

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

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

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.