SQL Saturday Holland 2015 - What's new in SSIS 2016 CTP 2.3

My SQL Saturday Holland 2015 presentation is now available for download. See link on sessions schedule. I had a great day and met lots of nice people! See you all on the next sql pass event.

Photo by @mstreutker

SQL Server 2016 CTP 2.3 New database roles SSISDB

Case
There are two new database roles available in the SSIS Catalog (ssis_logreader and ssis_monitor). What kind of permissions do these roles have and how should we use them?

ssis_logreader and ssis_monitor roles

Solution
The database role ssis_monitor is for an AlwaysOn situation to do some clean-up and update work. The role is used by the SQL Server Agent job "SSIS Failover Monitor Job" and you shouldn't use this role yourself.

But you can use the new database role ssis_logreader which allows a user to read reports in the catalog. If you don't have this role you can only see the reports with your own executions on it. A workaround for that was to give a user the database role ssis_admin, but allows you to play God in the catalog. This is where the new logreader role comes in handy. It allows you to see everybody's executions without the God-mode.

SQL Server 2016 CTP 2.3 OData V4 protocol

SSIS 2016 CTP 2.3 introduces an ODdata Source Component and an OData Connection Manager with V4 support. It now supports:

• OData V3 protocol for ATOM and JSON data formats.
• OData V4 protocol for JSON data format.

The 2014 version only supports V3.

SSIS 2014: Data at the root level is invalid. Line 1, position 1. (System.Xml)

 

First the OData Connection Manager. You can test this with the test URL: http://services.odata.org/V4/Northwind/Northwind.svc/
More info here.

OData Connection Manager with V4 support



 

 

 

And there is the OData Source Component that uses the OData Connection Manager. More info here.

OData Source

And now in action with a Data Viewer:

OData with Data Viewer

SQL Server 2016 CTP 2.3 Get error columnname

Case
Before SSIS 2016 there was no way to get the name of the column that caused the error. Atleast not without a custom transformation or a very complicated script that looped through a copy of your package to get all details. Now there is a simple solution available.

Solution
There was already a script available to get the error description. Now you can use a similar way to get the columnname. First download SSDT 2015 (SSDT and SSDT-BI have been merged!).

* UPDATE: code below outdated / changed click here for new code *

1) Data Flow Task
For this example we have a Flat File Source and to throw an error there is a column in the textfile with a too large value causing a truncation error. To catch the error details we redirect all errors of the Flat File Source to an Error Output. You can find these settings by editing the Flat File Source component or by connecting its red output to an other transformation.

Redirect errors to Error Output

2) Add Script Component
The Error Output is redirected to a Script Component (type transformation). It should look something like this below. Give it a suitable name like "SCR- Get Error Details".

Add Script Component Transformation

3) Input Columns
Edit the Script Components and go to the Input Columns page and select the ErrorCode (for getting an error description) and the ErrorColumn (for getting a column name) as ReadOnly input columns.

Input Columns

4) Output Columns
Create two output columns with the Data Type String (DT_STR). For this example I used 255 for the length, but that could probably be a little smaller. The names are ErrorDescription and ErrorColumnName.

Output Columns

5) The Code
Now go to the first page to choose your Scripting Language and then click on the Edit Script button to open the VSTA environment. Then locate the Input0_ProcessInputRow method at the bottom and add the following two lines of code.

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

And VB.NET code

' VB Code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    Row.ErrorColumnName = Me.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn)
End Sub

6) Testing
Now add a Data Viewer behind the Script Component Transformation to see the results

Error Description and ColumnName