visual studio - Trouble with Expression in SSIS Derived Column Transform Editor -

this question has answer here:

• ssis how part of string separator using derived column 3 answers

i need split 1 column 2 columns. data in column split dash (see below) , has parenthesis between text.

example of column: (data1) - (data2)

i have query used against database works, having trouble creating expression in ssis.

here queries have used in ssms generate new columns without dash , parenthesis:

to data on left side of column new column:

select substring(replace(replace(replace(column_name,'(',''),')',''),' ',''),0,charindex('-',   replace(replace(replace(column_name,'(',''),')',''),' ',''))) new_column_name  table 

to data on right of column new column:

select substring(replace(replace(replace(column_name,'(',''),')',''),' ',''),charindex('-',   replace(replace(replace(strekning_navn,'(',''),')',''),' ',''))+1,len(replace(replace(replace(column_name,'(',''),')',''),' ',''))) new_column_name  table 

can offer tips?

thank in advance.

i prefer solve string manipulation problems script task because code more robust , clean can done in sql or ssis expressions. go this:

1. add script task
2. add 2 string columns (i.e. "column1" , "column2")

3. do following in script task (assuming input column named "col":

   using system.text.regularexpressions;  public override void input0_processinputrow(input0buffer row) {     string[] columns = row.col.tostring().split('-');     regex reg = new regex(@"[\(\)]");     row.column1 = reg.replace(columns[0], "");     row.column2 = reg.replace(columns[1], "");  } 

so split function converts string array , can use regex remove parentheses.