How to use Substring in SSIS

Question

I want to export data from SharePoint list to SQL using SSIS. In SharePoint list, i have a column as multi select, So i am getting below value in my column

1;#control 1;#3;#control 3

I want to use substring in derived column in such a way that i should get below result

1,3

I want only ID from the given column.

I have tried below code

SUBSTRING(ColumnName,1,FINDSTRING(ColumnName,";#",1) - 1)

But it only gives me answer as

1

Can anyone please help me out.?

Answer 1

Because there is an unknown number of controls selected in your SharePoint Multi-Select, a Derived Column transformation is not going to work for you. You'll have to use a script.

One way to parse your string is with regular expressions. You'll have to add an output to the script transformation and assign your parsed string to that output.

        Regex controlExpression = new Regex(@"control ([0-9]+)");
        MatchCollection controlMatches = controlExpression.Matches(--YOUR INPUT HERE--);
        String output = string.Join(",", 
            (controlMatches.Cast<Match>().Select(n => n.Groups[1].ToString())).ToArray());