Search code examples
sql-serverssisssis-2012

Insert characters in SSIS

I have a requirement where I need to insert special characters for a particular column after the interval 2,3,3 example

ABCDEFGHIJKL -> AB-CDE-FGH-IJKL

I know I need to use Derived column in SSIS 2012 but I am stuck with the expression. I would really appreciate if anyone can help me out with the correct expression

Solution

  • The logic will be that you need to split your string at a given ordinal position into two pieces and then concatenate those pieces back together with your special character.

    A derived column is going to be a bit ugly because the language doesn't have the power of the .net libraries. We'll make heavy use and abuse of SUBSTRING to get the job done

    SUBSTRING(MyCol, 1, 2) + "-" + SUBSTRING(MyCol, 2, LEN(MyCol) -2)

    That applies the first special character logic. To simplify matters, I would add this column as MyColStep1 to the data flow. I then add a second Derived Column task that uses the above logic but instead uses MyColStep1 as the input. Taking this approach will make it much, much easier to debug (since you can attach a data viewer on the output path of each component).

    SUBSTRING(MyColStep1, 1, 6) + "-" + SUBSTRING(MyColStep1, 6, LEN(MyColStep1) -6)

    Etc.