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 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.
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)
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);
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.