CODEWITHSUNDEEP
sql-serverssisssis-2012
mehtat_90
mehtat_90

Reputation: 628

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

Upvotes: 0

Views: 1859

Answers (1)

billinkc
billinkc

Reputation: 61211

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.

Upvotes: 1

Related Questions