Thursday, July 21, 2011

ISNULL function not working in SSIS as it works in TSQL

ISNULL function not working in SSIS as it works in TSQL:


ISNULL Function in TSQL replaces the null value with other value.
For Example ISNULL (@value, ‘Name”) in this if there will be null value in @value it will be replaced by string ‘NAME’.
 While in SSIS when I used ISNULL function in execute SQL task’s SQL command it throws the error of wrong no of arguments supplied in the function.
In SSIS I SNULL function returns the Boolean value of true or false i.e. 0 or 1 and it takes only one argument  ISNULL(@Value) will be zero or one i.e. will let you no its null or not.
This is null function can be used in expression but not in TSQL for SSIS otherwise will throw the Error.
Work around for this approach will be:
Used script task create dynamic query, do the null operation in script task. Execute the dynamic query using variable as input type in Execute SQL Task.

3 comments:

  1. I was facing similar kind of isssue while working in SSIS project. Your Blog really helped.
    Thanks.

    Keep posting such things....!! :)

    -Tarun
    Artificer
    http://www.artificer.in

    ReplyDelete
  2. Good day! you may try this syntax.

    Thanks.

    Syntax:

    FirstName + " " + (ISNULL(MiddleName) ? "" : SUBSTRING(MiddleName,1,1) + ". ") + LastName

    ReplyDelete