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

SQL Server 2016 CTP 2.3 Custom Logging Levels

Case
In CTP 2.3 Custom logging levels where added. How does it work?

Solution
You can set it up in the Catalog with SQL Server Management Studio.

1) Customized Logging Level
Right click on the SSISDB Catalog and choose Customized Logging Level. A new window will be opened where you can add and change Customized Logging Levels.

Right click the SSISDB Catalog

2) Create
Click on the create button to add a name and description of your new Customized Logging Level. In this case we will create a logging level that only shows OnError events.

Only OnError

3) Edit
A new logging level has been created. You can click it to view and change the properties. Our first Customized Logging Level has ID 1.

Edit Customized Logging Level

4) Statistics
Go to the second tab to change the Statistics. In CTP 2.3 the checboxes on the individual rows seems to be missing. So I added them manually to the screenshot, but they do work when you use your Arrow keys to select the cell where they supose to be and then press [Space] to (un)check the individual item.


Select Statistics options

5) Events
Now go to the third tab to select the events you want to log. Same problem with the missing checkboxes here, but the same workaround works. In this case I only checked OnError events. When you're ready click on the Save button to save all changes. 

Select the Events

6) Execute
Now right click on your package to execute it and go to the Advanced tab to select the Logging Level. In the drop down list select the bottom item <Select customized logging level...>.

Select customized logging level...

7) Select customized logging level
A new popup window appears when you can select your newly created Cusomized Loging Level. Again there seems to be missing a checkbox here. By default the row seems to be already selected, so you don't have to selected it. If you do try to select it, you will uncheck it and you will get a warning:
No customized logging level is specified. Please select a customized logging level value before performing this operation.

8) View details customized logging level
When you click on the button with the three dots on the right side of the popup window, you will be able to see the details. Here the checkboxes are visible!

View details of your customized logging level

9) Execution results
Now it's time to see the result. As you can see: a customized logging level was selected and only errors are shown.

10) Feature request
Besides the hidden checkboxes I also would like to make my Customized Logging Level the default for the catalog. At the moment this seems not to be possible.

Default logging level

Update: Nice youtube video with Matt Masson showing the custom logging levels

SSIS 2016 CTP2 - Incremental Deployment

Case
Incremental Deployment was announced in CTP2. How do I test this without a new SSDT-BI?

Solution
You need to start ISDeploymentWizard.exe from [Drive]:\Program Files\Microsoft SQL Server\130\DTS\Binn

ISDeploymentWizard.exe from the 130 folder

In the second screen have to select Package Deployment. After that you can browse to your Visual Studio (SSDT-BI) folder where your SSIS 2014 project is located. You can also copy and paste the folderpath and then hit the Refresh button. Now you are able to uncheck some of the packages.

Incremental Deployment

*Update: in CTP 2.3 you can deploy a single package in the context menu of the solution explorer:

Deploy single package