将 SQL NULL 分配给 SSIS 字符串 - SSIS 缺陷? [英] Assigning a SQL NULL to a SSIS string - A SSIS Flaw?

问题描述

我通过执行 SQL 任务获取结果集.它只有一列 NullTime varchar.它有三行，第一行是 NULL.我想简单地迭代并显示这些行的值.如果我只用 C# 脚本来做，那么没有问题.NULL 显示为空白.同样的事情也可以用 foreach 循环来完成.

I fetch a result set with an execute SQL task. It has only one column NullTime varchar. It has three rows, first one is NULL. I want to simply iterate and display the value of these rows. If I do it only by C# script, then there is no problem. The NULL is displayed as a blank. The same thing can also be done with a foreach loop.

如何使用 foreach - 使用该循环读取每一行并将每一行的值设置为 SSIS 字符串 User::STR_WORD.然后，只需使用 C# 脚本任务显示 User::STR_WORD.

How to do it with foreach - use that loop to read each row and set the value of each row to SSIS string User::STR_WORD. Then, simply display User::STR_WORD with a C# script task.

在纯 C# 方法中，我什至可以将空白值(实际上是 NULL)分配给 SSIS 字符串.但是对于 foreach 循环方法，由于 NULL 值，我得到了一个错误.

In the pure C# method, I can even assign the blank value (actually a NULL) to the SSIS string. But with foreach loop method, I get an error because of the NULL value.

错误是 -

错误:分配给变量User::STR_WORD"的值类型与当前变量类型不同.变量在执行期间可能不会改变类型.变量类型是严格的，Object 类型的变量除外.

Error: The type of the value being assigned to variable "User::STR_WORD" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

我该如何解决这个错误?脚本是似乎有缺陷的唯一替代方案吗?循环?

How do I fix this error ? Is script the only alternative to what seems to be a flawed for loop ?

推荐答案

解决方案是 Coalesce 函数，它将 NULL 转换为您指定的值.

A workaround for this is the Coalesce function which will convert NULLs to the value you specify.

SELECT
COALESCE([YourColumn], 'EnterValueHere') AS [YourColumn]
FROM YourTable

因此，这会将 null 替换为您要使用的值.它会防止奇怪的 FOR 循环突然崩溃.

So, this will replace a null with the value you want to use instead. It will prevent the weird FOR loop from suddenly crashing.

创建一个临时表来看看这个工作 -

Create a temporary table to see this working -

create table ##tester
(names varchar(25))
insert into ##tester
values(Null)
insert into ##tester
values('Not a null value')
select names from ##tester
select coalesce(names, 'Null has been obliterated!') from ##tester 

这篇关于将 SQL NULL 分配给 SSIS 字符串 - SSIS 缺陷?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！