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

libpekin 86 Reputation points
2024-06-23T15:27:18.92+00:00

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

User's image

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,502 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 25,721 Reputation points
    2024-06-23T18:03:41.8+00:00

    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)

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest