CODEWITHSUNDEEP
sql-serverssis
Avinash
Avinash

Reputation: 553

Concatenation multiple columns in SSIS

I'm doing a simple concatenation in SSIS.

For example I have a table like this:

+--------+-----------+------------+--------+
| ID     | COL_A     | COL_B      | COL_C  |
+--------+-----------+------------+--------+
| 110-99 |           | APPLE      | Orange |
+--------+-----------+------------+--------+
| 111-01 | Mango     | Palm       |        |
+--------+-----------+------------+--------+
| 111-02 |           | Strawberry |        |
+--------+-----------+------------+--------+
| 111-05 | Pineapple | Guava      | Lemom  |
+--------+-----------+------------+--------+

I'm doing this in SSIS Derived column. Concatenation of 3 columns with Pipe |

COL_A +"|"+COL_B+"|"+COL_C

Actual Result:

|APPLE|Orange
MANGO|Palm|
|Strawberry|
Pineapple|Guava|Lemom

Expected Result:

APPLE|Orange
MANGO|Palm
Strawberry
Pineapple|Guava|Lemom

I'm not sure how to remove those extra | when the value is empty. I have tried using CASE but it is not working. Actually I don't know how to use CASE in Derived column expression.

Upvotes: 0

Views: 1257

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

You execute conditional logic in SSIS expressions using ?: syntax. ? : (Conditional) (SSIS Expression) It works much like an inline IFF.

Along these lines:

boolean_expression ? returnIfTrue : returnIfFalse

In order to get your desired results, I think I'd use two derived column transformations. In the first one, I'd create the pipe delimited string, then in the second one, I'd trim off the trailing pipe if there was one after building the string. Otherwise, the conditional logic would get pretty hairy in order to avoid leaving a trailing delimiter.

Step one - If each column is NULL or an empty string, return an empty string. Otherwise, return the contents of the column with a pipe concatenated to it:

((ISNULL(COL_A) || COL_A == "") ? "" : COL_A + "|"

Repeat that logic for all three columns, putting this expression into your derived column (Line breaks added for readability here):

(((ISNULL(COL_A) || COL_A == "") ? "" : COL_A + "|" ) + 
(((ISNULL(COL_B) || COL_B == "") ? "" : COL_B + "|" ) + 
(((ISNULL(COL_C) || COL_C == "") ? "" : COL_C )     --No pipe here, since nothing follows.

Then, in the second transformation, trim the trailing pipes from the instances where the last column or two were empty:

(RIGHT(NewColumnFromAbove,1)=="|") ? LEFT(NewColumnFromAbove,LEN(NewColumnFromAbove)-1) : NewColumnFromAbove

On the other hand, if there are lots of columns, or if performance gets bogged down, I would strongly consider writing the concatenation into a stored procedure, using CONCAT_WS, and then invoke that from an Execute SQL task instead.

Upvotes: 1

GMB
GMB

Reputation: 222702

In SQL Server, one option is concat_ws(), which ignores null values by design. If you have empty strings, your can turn them to null values with nullif().

concat_ws(
    ' | ', 
    nullif(col_a, ''),
    nullif(col_b, ''),
    nullif(col_c, '')
)

Upvotes: 1

Related Questions