-1

I need to split up one column into two columns. The data in the column is currently split by a dash (see below) and has parenthesis between the text.

Example of column: (Data1) - (Data2)

I have a Query that I used against the database that Works, but I am having trouble creating this Expression in SSIS.

Here are the queries I have used in SSMS that generate the New columns without the dash and parenthesis:

To get the data on the left side of the column into a New column:

SELECT SUBSTRING(REPLACE(REPLACE(REPLACE(COLUMN_NAME,'(',''),')',''),' ',''),0,CHARINDEX('-', 

REPLACE(REPLACE(REPLACE(COLUMN_NAME,'(',''),')',''),' ',''))) AS NEW_COLUMN_NAME

FROM TABLE

To get the data on the right of the column into a New column:

SELECT SUBSTRING(REPLACE(REPLACE(REPLACE(COLUMN_NAME,'(',''),')',''),' ',''),CHARINDEX('-', 

REPLACE(REPLACE(REPLACE(STREKNING_NAVN,'(',''),')',''),' ',''))+1,LEN(REPLACE(REPLACE(REPLACE(COLUMN_NAME,'(',''),')',''),' ',''))) AS NEW_COLUMN_NAME

FROM TABLE

Can anyone offer any tips?

Thank you in advance.

Izuka
  • 2,572
  • 5
  • 29
  • 34
drtorsk
  • 1
  • 3
  • In general I've had no issues turning a normal query into a SSIS query. Have you thought about using PATINDEX maybe? – ste-fu Jan 27 '16 at 12:58
  • Which Version of SSIS are you using? Using Visual Studio 2014, and I have never used a normal Query in the Derived Column Transform Editor – drtorsk Jan 27 '16 at 13:05
  • Why not do this operation in the database instead of in a Derived Column Transform? – Tab Alleman Jan 27 '16 at 14:08
  • also http://stackoverflow.com/questions/10921645/ssis-how-to-get-part-of-a-string-by-separator – Tab Alleman Jan 27 '16 at 14:10

1 Answers1

0

I prefer to solve string manipulation problems with a script task because the code is much more robust and clean than anything that can be done in SQL or SSIS expressions. So it would go something like this:

  1. Add a Script Task
  2. Add two string columns (i.e. "Column1" and "Column2")

  3. Do the following in a script task (assuming input column is 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 the split function converts the string to an array and we can use regex to remove the parentheses.

Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25