我正在使用派生的列来连接两个WSTR列，这两种列都可以为空。
警报（3000个宽度列）和customAlert（125宽列）
我遇到的问题是，当我使用替换替换运行时，如果其中一个列为null，则串联适当地发生，它将每种计算的目标列宽度加倍列，将派生的列推到WSTR列的极限之外（因此，对于书面的表达式，它想要6254列宽度，而不可能在不移至NTEXT的情况下，当我必须将其转换回NTEXT时，这会导致其自身的问题。。
REPLACENULL(Alert,"") + REPLACENULL(customAlert,"")
我可以在数据流中使用C＃脚本来解决此工作以完成工作，但是我宁愿理解为什么会发生这种情况。
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.
发布评论
评论（1）
恭喜，我很少会学习有关SSIS的新知识。
肯定感觉就像替换表达式中有一个错误，但我也不知道它的存在
设置，
我使用以下查询使用以下查询来定义数据流
，从而导致
i i可以同意提供的表达式失败，涉及结果字符串的无效长度的错误。我的大脑真的很奇怪的是，正如您所说的6254。这并不是一个完美的加倍，所以...我不知道那里发生了什么。
我主张将SSIS中的东西分解为较小的零件，因为它们使调试变得更加容易。
我首先将您的3个操作分为2个单独的操作，以便在以后的一步中注视着串联。
马上，这个单独的错误。
大约错误
6002？我真的不知道幕后发生了什么。我们将其固定并回到它。
我添加了
，这本身就起作用了，但长度再次加倍 + 2，预期宽度为252。
因此，我们不能将替代品与警报一起使用，并与customAlert一起使用它会使我们的空间加倍。
我们还能做什么？
发明一台时间机器并在SSIS 2005上切牙。;;）解决问题的另一种方法是使用三元操作员
测试条件？ true子句：false子句和
是NENULL（CustomAlert）？ “”：CustomAlert
分别产生长度3000和125的列。就我个人而言，我将在将这两个新列和然后的串联添加到一个列表之后，因为您遇到的下一个错误将使多合一表达式变得更加困难为了诊断，
其正确长度为3125
替换它是否确定了这种意外行为。使用SQL Server 2017测试
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
That results in pipeline metadata of
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.
Error approximately
6002? I really have no idea what's going on behind the scenes. We'll pin that and come back to it.
I added
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 clauseand
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
This has a correct length of 3125
Nowhere in the documentation for REPLACENULL does it identify this unexpected behaviour. Tested using SQL SERVER 2017