Concatenate columns using derived column in ssis

Question

What I am trying to achieve is Column 5:

Column 1 Columns 2 Column 3 Column 4  Column 5
A        B                  D         A|B|D

I used a derived column:

(DT_STR,50,1252)([A] + "|" + [B] + "|" + [C] + "|" + [D])

But I ended with:

Column 1 Columns 2 Column 3 Column 4  Column 5
A        B                  D         A|B||D

I'm struggling to find the right solution.

Answer 1

Your problem is that have an empty column of data and you've made no attempt to test or handle it in your concatenation.

If you know you'll only ever have one empty column in a row, the following expression would correct it. It simply adds a REPLACE call to the resulting string to make the double pipes into a single pipe.

(DT_STR,50,1252)REPLACE(([Column 1] + "|" + [Column 2] + "|" + [Column 3] + "|" + [Column 4]),"||","|")

Otherwise, you're going to need to check to see whether the column has a value and only then do you add the delimiter. That'd result in an expression like the following

(DT_STR,50,1252)(([Column 1] + ((LEN([Column 1]) == 0) ? "" : "|") + [Column 2] + ((LEN([Column 2]) == 0) ? "" : "|") + [Column 3] + ((LEN([Column 3]) == 0) ? "" : "|") + [Column 4]))

Given the following sample data

SELECT
    'A' AS [Column 1]
,   'B' AS [Column 2]
,   '' AS [Column 3]
,   'D' AS [Column 4]
UNION ALL
SELECT
    'A' AS [Column 1]
,   '' AS [Column 2]
,   '' AS [Column 3]
,   'D' AS [Column 4]
UNION ALL
SELECT
    'A' AS [Column 1]
,   '' AS [Column 2]
,   '' AS [Column 3]
,   '' AS [Column 4]
UNION ALL
SELECT
    '' AS [Column 1]
,   '' AS [Column 2]
,   '' AS [Column 3]
,   'D' AS [Column 4];

Generated the following results

At this point, I'd probably skip the derived column and use a Script Component to generate the concatenated data. Expression's that scroll are maintenance challenges. Plus, the above scripts don't factor in NULLs.

Answer 2

This should do the trick:

(DT_STR,50,1252)((Column1 == "" ? "" : Column1 + "|") + (Column2 == "" ? "" : Column2 + "|") + (Column3 == "" ? "" : Column3 + "|") + (Column4 == "" ? "" : Column4))

Basically, you need to check for an empty value in each column and concatenate with either empty string or the column content, and an optional separator depending on column location.