SSIS Derived Column (if then…else)

Question

is there a way of replicating the below expression in SSIS Derived Column?

SELECT CASE
           WHEN LEN(column1) > 8
               THEN
                   column1
               ELSE
                   REPLICATE('0', 18 - LEN(column1)) + column1
           END AS column1
FROM myTable

I want to pad the value of column1 with 0 if the lenght of the value is less than 8 character

Answer 1

The SSIS expression language supports the ternary operator ? :

(LEN([column1]) > 8) ? column1 : replicate("0", (18 - LEN([column1]))) + [column1]

That expression ought to work but it doesn't because the REPLICATE call is going to provide metadata back stating it's 4k nvarchar characters (the limit). If you're deadset on getting it to work that way, comment and I'll hack the expression to size the output of replicate before concatenating with column1

An easier approach is to just always add 8 leading zeros to the expression and then slice it off from the right.

RIGHT(REPLICATE("0",8) + column1,8)

You might need 18 on the second as your example seemed to use 18 but the concept will be the same.

Answer 2

This is an old question, but I had the same question today and the below Derived Column Expression is what worked for me. It doesn't specifically "replicate" the SQL example in the original question (by using "REPLICATE") but I found the below to be a little simpler for padding a value using an SSIS Derived Column in a Data Flow, and it's a good example of a simple "If / Else" statement. I am padding with 10 zeros because that's what the SQL does in the original question.

(LEN(column1) > 8) ? column1 : ("0000000000" + (column1))