Concatenating columns using a derived column in ssis

Question

What I'm trying to achieve is Column 5:

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

I used the derived column:

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

But I ended up with:

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

I am trying to find the right solution.

Answer 1

This should do the trick:

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

Basically, you need to check the null value in each column and combine with an empty row or column contents and an optional delimiter depending on the location of the column.

Answer 2

Your problem is that you have an empty data column and you have not tried to check or process it in your concatenation.

, , . REPLACE , .

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

, , . ,

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

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];

, , Script Component . , , . , NULL.