Concatenate columns using derived column in ssis

2019-06-17 02:37发布

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.

标签: ssis
2条回答
戒情不戒烟
2楼-- · 2019-06-17 03:18

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

enter image description here

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.

查看更多
啃猪蹄的小仙女
3楼-- · 2019-06-17 03:34

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.

查看更多
登录 后发表回答