Assigning a SQL NULL to a SSIS string - A SSIS Fla

问题:

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.

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.

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.

The error is -

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 ?

回答1:

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

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