Post reply

SSIS and Null values

  • February 11, 2014 at 8:27 am

    #288122

    Hi Everyone,

    I have package in SSIS. It has a execute sql task that gets some config values from a table on different server. So the SSIS package gets those config values and stores them in the variables in SSIS. These variables are used in further sql tasks as parameters.

    The issue that I am encountering is, some of those config values are null. So whenever SSIS encounters a null value for a integer type, it converts that to 0 and passes it as 0 to further task rather than passing it as null.

    If the data type of the column is date and its value is null in the config table, SSIS assigns a default value “12/30/1899 12:00:00 AM” and passes the default value as the parameter rather than the null value.

    How do I make the SSIS package pass the actual null value rather than the value converted by SSIS ?

    Thanks in advance!!

  • February 11, 2014 at 9:52 am

    #1687989

    Are the default values that the configuration gives you ones that you could ever picture using? Would you ever use 0, or the date in 1899? If not, then you can convert those values to null using nullif in your execute SQL task

    Russel Loski, MCSE Business Intelligence, Data Platform

  • February 11, 2014 at 10:56 am

    #1688028

    Thanks for the reply Russel. I thought of that. We dont use the default date for sure but there are chances that we might use 0 for config values. Is there any way around or any setting in SSIS to overcome this issue. Why does SSIS do this conversion.

  • February 11, 2014 at 10:59 am

    #1688031

    I think that the variables cannot contain nulls.

    You could replace your configurations with some value that you aren't going to use (-1, -100000, or some such).

    Russel Loski, MCSE Business Intelligence, Data Platform

  • February 11, 2014 at 11:08 am

    #1688037

    Thanks so much for the reply Russel. I am thinking this is not a unique situation which i have encountered. The solution that you have provided will work in my scenario.

    Thanks so much for you time :)..

  • February 11, 2014 at 1:14 pm

    #1688094

    Just FYI: The only variable type in SSIS that's allowed to contain a NULL value is the Object variable, which is why you're not able to pass it in via the configuration and it defaults.

    - 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 6 posts - 1 through 5 (of 5 total)

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