Post reply

Assigning an SQL NULL to an SSIS string - An SSIS Flaw ?

  • February 6, 2014 at 4:37 pm

    #282024

    Hi,

    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 ?

  • February 6, 2014 at 4:49 pm

    #1686853

    I assume you're dumping to a recordset object for the ForEach loop?

    Swap the datatype before you include it into the object. (DT_str, 1252) instead of (DT_Date).

    The problem is more complex than it looks. The short form is the data type change is what's causing the issue, and it doesn't want to do that with nulls without an explicit call.

    Either that, or set USER::STR_WORD as a Date type, not a string type.

    C# has more controls wrapped around translations, in particular for empty variables (NULL), thus why the more robust language handles it better.

    A weakness in the engine, perhaps, but I'm not sure I'd bring it to flaw levels. You just have to know to expect to do your own conversions.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply