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

Question

Hello,

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[vendorName]".

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.

Answer 1

Hi @wiseguy,

It is easy to achieve by using REPLACE(), TOKEN() and TOKENCOUNT() functions.

SSIS Expression along the following will do it (needs to be tested):

TOKEN(REPLACE([ErrorColumn], "[","]") , "]", TOKENCOUNT(REPLACE([ErrorColumn], "[","]") , "]") - 1)