SSIS Run string as Expression

Question

I have replaced formulas by the respective values, ending up with a large column of this:

8/4
8+4
9*84
48/74
(8+5)/7
(4*150,5)/(8,05/4,08)

I need to handle these strings (WSTR) as a regular expression, ending up with one numeric value.

Originally this was done in VBA with the eval function.

---Edit since answer:

Have to admit i always avoided the Script Component because i've never gotten it to work.

I tried now and with your link got to this code:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{

    Row.OutM01 = Evaluate(Row.M01);

}
static double Evaluate(string expression)
{
    var loDataTable = new DataTable();
    var loDataColumn = new DataColumn("Eval", typeof(double), expression);
    loDataTable.Columns.Add(loDataColumn);
    loDataTable.Rows.Add(0);
    return (double)(loDataTable.Rows[0]["Eval"]);
}

Yet, Specified cast is not valid or syntax errors is the only thing i ever get.

If your OutM01 column is a string datatype, you need to use convert the output of the Evaluate() function to a string: Row.OutM01 = Evaluate(Row.M01).ToString(); — Bert Wagner, Apr 17 '15 at 15:42

score 0 · Accepted Answer · edited May 23 '17 at 12:06

Using C# in a Script Component you can evaluate a string many different ways: Evaluating string "3*(4+2)" yield int 18

EDIT: Using one of the solutions from the above link that don't require an external framework/library:

In your ScriptComponent, replace the Input0_ProcessInputRow(Input0Buffer Row) method with:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
     Row.Formula = Evaluate(Row.Formula).ToString();
}

static double Evaluate(string expression)
{
    var loDataTable = new DataTable();
    var loDataColumn = new DataColumn("Eval", typeof(double), expression);
    loDataTable.Columns.Add(loDataColumn);
    loDataTable.Rows.Add(0);
    return (double)(loDataTable.Rows[0]["Eval"]);
}

This assumes your input column with the expression in it is called "Formula" and you have defined it as an InputColumn that can written to in your Script Component:

InputColumn setup

I tried to write it in the script component without success, really no way to use Another tool from the SSIS Toolbox to achieve this? See First Post — ben.w, Apr 17 '15 at 14:24

score 0 · Answer 2 · answered Apr 17 '15 at 15:42

public override void Input0_ProcessInputRow(Input0Buffer Row)
{

    //Row.M01 = Evaluate(Row.INM01);
    if (Row.INM01 == null)
    {
        //Row.M01 = null;
    }
    else
    {
        string s = Row.INM01;
        s = s.Replace(",", ".");
        Row.M01 = Evaluate(s);

    }

}
static double Evaluate(string expression)
{
    DataTable MyTable = new DataTable();
    DataColumn MyColumn = new DataColumn();
    MyColumn.ColumnName = "MyColumn";
    MyColumn.Expression = expression;
    MyColumn.DataType = typeof(double);
    MyTable.Columns.Add(MyColumn);
    DataRow MyRow = MyTable.NewRow();
    MyTable.Rows.Add(MyRow);
    return (double)(MyTable.Rows[0]["MyColumn"]);
}

Turns out i had to replace the comma with point.

SSIS Run string as Expression

2 Answers2