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