Derived column Transformation Editor - I need add number at the end of order number in increment order

Question

I have a csv file having many lines with different order number

I need to change them via SSIS Derived column Transformation Editor so I can have transformed output.

I need to write Expression that adds number at the end of order but I need different number or another order so it should be increment

Derived column Name    Derived Column         Expression             Data Type  
OrderNumber           <add as new column>     ?  


Derived column Name    Derived Column         Expression             Data Type  
OrderNumber           <add as new column>     OrderNumber+"-"+"1"    unicode string

Answer 1

There is no stock method to do what you are trying to achieve. What would rather be easier is to write a script component and have that generate the numbers for you. Once you get the new column out of that script component, it is easy enough to concatenate that with your existing order number.

Just curious, why cant you do this on the database itself? It would be much easier to implement and control IMO.

Here is a link to generating the numbers: Generating Surrogate keys in SSIS

Answer 2

I don't think you can add an incremental number using derived column transformation, you have to use a script component to achieve that.

Simply add a script component, go to Inputs and Outputs tab and add an Output column of type DT_STR. And inside the script editor use a similar script:

int intOrder = 1;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{

if(!Row.OrderNumber_IsNull && !String.IsNullOrEmpty(Row.OrderNumber)){

    Row.outOrderNumber = Row.OrderNumber + "-" + intOrder.ToString();
    intOrder++;

}else{

    Row.outOrderNumber_IsNull = true;

}

}