Return End of String Between 2 Brackets - SSIS Derived Column Expression (SSIS 2022)

Question

The difficulty of extracting the error column name in an SSIS data flow is widely recognized. To meet the requirement of minimizing scripting transforms/components, I cannot use a complex scripting solution to identify error columns in the dataflow. However, by utilizing two lines of C# that are well documented, I can obtain the Error Column description, which is the value shown in the Data Viewer—a string with the column name at the end, such as:

"OLEDB_DEST - Load Raw Dimension.Inputs[OLE DB Destination Input].Columns[ProductID]".

Below is my code in the ProcessInputRow method

    public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Get the error description 
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    // Get the error column name  
    IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

}

I am seeking a method to use the Derived Column transformation to consistently extract the string between the last two brackets, which is the column name. Any help is appreciated.

Data viewer image

Thank you!

I've tried using SUBSTRING AND FINDSTRING but without success.

Answer 1

You can add a couple of more lines to the script that you are using to extract the ErrorColumn - Description which will be simpler than using a derived column:

IDTSComponentMetaData130 componentMetaData130 = this.ComponentMetaData as IDTSComponentMetaData130;
string errorColumn = componentMetaData130.GetIdentificationStringByID(Row.ErrorColumn);
int start = errorColumn.LastIndexOf('[') + 1;
int end = errorColumn.LastIndexOf(']');
Row.ErrorColumnName = errorColumn.Substring(start, end - start);

EDIT: Adding screenshots for clarification:

1. Configure source component to redirect rows to script component (transformation)

2. In script component, select ErrorColumn so we can get the source id.

3. Add a column to the output for ErrorColumnName

4.The ProcessInputRow method should look like this:

5. Final output should look like this: