SSIS Data Streaming Destination

Case
What can you do with the Data Streaming Destination in SSIS and how does it work? It only has an 'Advanced' editor with very little explanation.

Editor Data Streaming Destination

Solution
You probably never used or even saw this destination component before because it was a separate download in SSIS 2012 and for SSIS 2014 I couldn't even find the download page. But now for SSIS 2016 it's one of the standard toolbox items. And even now it's in the wrong toolbox section: you will find it under Common instead of Other Destinations. That can be easily solved by moving it the appropriate section of the SSIS Toolbox.

Moving Data Streaming Destination

The Data Streaming Destination allows you to query its output via a linked server connection on the SSISDB. If certain sources or transformations are very hard with standard TSQL then you could solve it in an SSIS package. After that you can query its output with TSQL. Yes I know almost every thing is possible with TSQL when you for example use CLR stored procedures, but SSIS is just a visual alternative.


1) Data Flow Task
For this example I will use a package with one very basic Data Flow Task with a Flat File Source Component, a Derived Column and the Data Streaming Destination.

Simple Data Flow with Data Streaming Destination

2) Data Streaming Destination
When you add the Data Streaming Destination and edit it, you will get the Advanced Editor. It only allows you to choose the input columns and you can change the name of an automatically generated identity column (see previous screenshot). For this example I pass through all columns and leave the id column name unchanged.

Pass through all columns

3) Saving and deploying
Now I need to deploy the package(s) to the SSIS Catalog. You could add parameters and create an SSIS Catalog environment to fill them on package start. I will skip that for this basic example.

Deploying packages to SSIS Catalog

4) Data Feed Publishing Wizard
Now start the SQL Server Integration Service Data Feed Publishing Wizard from the start menu and choose which package to execute in your view. You also need to provide the name and location of your view. For Linked Server validation errors go to step 4b.
(C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\ISDataFeedPublishingWizard.exe)

SSIS Data Feed Publishing Wizard

4b) Error: The Allow Inprocess option for the OLE DB Provider is not enabled.
When you get a Linked Server error during validation you need to enable "Allow inprocess".

The Allow Inprocess option for

the OLE DB Provider is not enabled.

Go to SSMS and connect to your server where the SSISDB is running. Expand Server Objects, Linked Servers, Providers and then right click SSISOLEDB and choose Properties. In the Provider Options enable "Allow inprocess" and click OK. After that Rerun volition in the Wizard.

Provider Options, Enable "Allow inprocess"

4c) Error: The are more than one Data Streaming Destination components in the package and only one of them can pass data to a SQL Server database
The error says it all: You can only have one Data Streaming Destination! Remove the others first and rerun validation.

The are more than one Data Streaming Destination components

in the package and only one of them can pass data

to a SQL Server database

5) Testing the view
Go to SSMS and execute your newly created view. You probably have to test you patience a little bit because it's not very fast. It first has to execute the package which takes a couple of seconds. I haven't found a good purpose for a real-life situation yet, but may be you can use it to create a (nearly) real time data feed from a webservice for your PowerBI report. Or..... let me know in the comments where you used it for.

Querying the new view 

If you don't want to use the wizard you could just do it with TSQL:

USE [SSISJoost]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[MyDataStream] AS SELECT * FROM OPENQUERY([Default Linked Server for Integration Services], N'Folder=DataStream;Project=DataStream;Package=DataStream.dtsx')
GO

Getting error column name in SSIS 2016

Case
In SQL 2016 CTP 2.3 Microsoft introduced a new simple way with to get the name of the column causing the error with some .NET code in a Script Component. In the final release this code doesn't work.

Solution
Not sure why, but they changed the code. Instead of one line we now need two lines. Below the complete example with the 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 three lines of code to it.

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Getting description already worked in previous versions of SSIS
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

    // componentMetaData (starting with a lowercase "c") is just a name.
    // You can change that name if you like, but also change it in the
    // second row.
    IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

And VB.NET code

' VB Code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Getting description already worked in previous versions of SSIS
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)

    ' componentMetaData (starting with a lowercase "c") Is just a name.
    ' You can change that name if you Like, but also change it in the
    ' second row.
    Dim componentMetaData As IDTSComponentMetaData130 = TryCast(Me.ComponentMetaData, IDTSComponentMetaData130)
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn)
End Sub

6) Testing
Close the VSTA environment to save the code and press OK the close editor. Now add a Data Viewer behind the Script Component Transformation to see the results

Error Description and ColumnName

TSQL Snippet: Split string in records

Case
I have a string in TSQL and I want to split it into separate values / records. How do I do that?

Solution
There are a lot of split examples available on the web, but I really like the XQuery solution for this. First you add a begin XML tag in front of your list and an closing XML tag at the end. Then you replace all separators by a closing and a begin tag. After that you have an XML string and you can use Xquery to split it. Below a little snippet as part of a stored procedure, but you could also create a function for it or just use the three lines in your own code:

-- Snippet
CREATE PROCEDURE [dbo].[SplitList] (
      @List VARCHAR(255)
    , @Separator VARCHAR(1)
)
as
BEGIN
    DECLARE @Split XML;
    SET @Split = CAST('<t>' + REPLACE(@List, @Separator, '</t><t>') + '</t>' as XML) 
    SELECT Col.value('.', 'VARCHAR(255)') as ListValue FROM @Split.nodes('t') as xmlData(Col)  order by 1
END

Note: your string / list can't contain forbidden XML characters like <, > and &. You could use additional REPLACE functions to prevent errors: REPLACE(@List,"<", "&lt;")

split snippet