SSIS Derived column padding of leading zeroes

Question

I want to be able to add leading zeroes in following scenarios -

1. I need leading zeroes before [ACCOUNT ID] if [ACCOUNT ID] < 17 {For APPLICATION = RCC, SEC, HOGAN CIS}

2. I need leading zeroes before [ACCOUNT ID] if ([ACCOUNT ID] + [ACCOUNT NUMBER]) < 17 {For APPLICATION = CLN}

3. I need leading zeroes before [CLIENT KEY] if ([CLIENT KEY] + [CLIENT ID]) < 17 {For APPLICATION = ITF}

I have the following expression defined in my derived column -

 LTRIM(RTRIM(APPLICATION == "RCC" || APPLICATION == "SEC" ? APPLICATION + "|" + [ACCOUNT ID] : APPLICATION == "HOGAN CIS" ? (HOGAN_Hogan_Alpha_Product_Code == "DDA" ? "DDA" : "TDA") + "|" + [ACCOUNT ID] : APPLICATION == "ITF" ? APPLICATION + "|" + [CLIENT KEY] + [CLIENT ID] : APPLICATION == "CLN" ? APPLICATION + "|" + [ACCOUNT ID] + [ACCOUNT NUMBER] : APPLICATION + "|" + [ACCOUNT NUMBER]))

Answer 1

Padding strings

The general strategy I use when dealing with adding leading characters is to always add them and then take the N rightmost characters. I find it greatly simplifies your logic.

RIGHT(REPLICATE('0', 17) + [MyColumn], 17)

That would generate 17 zeros, prepend that to my column and then slice off the last 17 characters. If MyColumn was already 17 character, then no effective work is done. If it was empty, now you have a value of 17 zeros.

Choosing

In your case, I'd first try add a derived column to identify which block of logic this APPLICATION falls into. Much like the existing ternary expression you have.

(APPLICATION == "CLN") ? 10 : 
    (APPLICATION == "ITF") ? 20 :
    (APPLICATION == "RCC" | APPLICATION == "SEC" ....) ? 30 : 40

Coming out of that derived column, you'll be able to verify the logic is working as expected which makes the padding an easier scenario.