SSIS Derived Column - REPLACENULL doubling column width

Question

I'm using a derived column to concatenate two WSTR columns, either of which could be NULL. Alert (3000 width column) and customAlert (125 wide column)

The problem I'm having is that when I run this with REPLACENULL so that the concatenation happens properly if one of the columns is NULL, it's doubling the calculated destination column width for each column, which is pushing the derived column outside the limit for a WSTR column (so for the expression as written, it wants 6254 column width, which is not possible without moving to NTEXT, which causes its own problems when I have to convert it back.) The column size requirements don't change if I don't use REPLACENULL, but that comes with other problems.

REPLACENULL(Alert,"") + REPLACENULL(customAlert,"")

I could work around this using a C# script in the data flow to do the work, but I'd rather understand why this is happening.

user_181965 · Answer

Congratulations, it is not often that I get to learn something new about SSIS.

It sure feels like there's a bug in the REPLACENULL expression but I was also unaware of its existence

Set up

I defined a data flow with an OLE DB Source using the following query

SELECT
    REPLICATE(N'A', 3000) AS Alert
,   REPLICATE(N'C', 125) AS customAlert;

That results in pipeline metadata of

Alert - DT_WSTR 3000
customAlert - DT_WSTR 125

I can concur that as provided the expression fails out with an error about the invalid length of the resulting string. What's really weird to my brain is that the computed string length, is as you say, 6254. That's not a perfect doubling so ... I have no idea what is going on there.

I advocate for breaking things down in SSIS to smaller pieces as they make debugging easier.

I started by splitting your 3 operations into 2 separate ones with an eye towards the concatenation in a later step.

Right off the bat, this alone errors out.

REPLACENULL(Alert, "")

Error approximately

the length is not valid. Must be between 0 and 4000. Computed length 6002

6002? I really have no idea what's going on behind the scenes. We'll pin that and come back to it.

I added

REPLACENULL(customAlert, "")

and that worked by itself but once again the length is doubled + 2 for an expected width of 252.

So, we can't use replacenull with Alert and using it with customAlert doubles our space.

What else can we do?

Invent a time machine and cut your teeth on SSIS 2005. ;) A different way of solving the problem is to use the ternary operator test condition ? true clause : false clause

ISNULL(Alert) ? "" : Alert

and ISNULL(customAlert) ? "" : customAlert

That results in a column of length 3000 and 125 respectively. Personally, I'd add a derived column after the one that makes these two new columns and then the concatenation because the next error you run into, is going to make the all-in-one expression more difficult to diagnose

(ISNULL(Alert) ? "" : Alert ) + (ISNULL(customAlert) ? "" : customAlert )

This has a correct length of 3125

Nowhere in the documentation for REPLACENULL does it identify this unexpected behaviour. Tested using SQL SERVER 2017

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer Version 14.0.3002.92

SSIS Derived Column - REPLACENULL doubling column width

1 Answers

Set up

What else can we do?