none
replace null with blanks

    Question

  • I am loading excel files into a SQL database as tables using SSIS.

    In the excel file some values in a every column were blank. After loading this data into the database table the blanks are appearing as "NULL". My task is to get rid of these "NULL"s and replace them with blanks.

    What transformations in SSIS can be used to solve this issue?

    NOTE: I want to solve this problem using SSIS and not SQL or excel.


    Dhananjay Rele

    Thursday, June 27, 2013 3:26 PM

Answers

  • yeh iv left a new reply there too

    there is a column with float datatype, this one woluldnt accept the expression, the other columns which are varchar accepted the expression.

    is thher a different expression for float datatype?


    Dhananjay Rele


    there is no blank value within a float... just null... you have to cast it to a string first.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, June 27, 2013 7:12 PM
    Moderator
  • Arthur,

    one of these fields was a float, and seems like this transformation works only for varchar values.

    Do we have any workaround for this?

    to get rid of nulls in a float value?


    Dhananjay Rele

    You cannot put blank for a float field. In that case you have to put a default value (say -1.0) using following expression:

    ISNULL(FloatFieldName)? -1.0 : FloatFieldName


    Nitesh Rai- Please mark the post as answered if it answers your question

    Friday, June 28, 2013 7:45 AM

All replies

  • You can use a Derived Column Transformation with the following expression

    ISNULL([EMPLOYEE FNAME]) ? " " : [EMPLOYEE FNAME] 


    Arthur My Blog

    • Proposed as answer by Nitesh Rai Thursday, June 27, 2013 3:45 PM
    Thursday, June 27, 2013 3:37 PM
    Moderator
  • Hi Arthur,

    is that a space between the quotes? or is it just 2 quotes?

    and if it is a space, do i need to TRIM it before sending it to the destination?


    Dhananjay Rele

    Thursday, June 27, 2013 3:44 PM
  • I don't think Artur has mentioned space. It is just a blank string "".

    Nitesh Rai- Please mark the post as answered if it answers your question

    Thursday, June 27, 2013 3:46 PM
  • Arthur,

    one of these fields was a float, and seems like this transformation works only for varchar values.

    Do we have any workaround for this?

    to get rid of nulls in a float value?


    Dhananjay Rele

    Thursday, June 27, 2013 5:06 PM
  • I am loading excel files into a SQL database as tables using SSIS.

    In the excel file some values in a every column were blank. After loading this data into the database table the blanks are appearing as "NULL". My task is to get rid of these "NULL"s and replace them with blanks.

    What transformations in SSIS can be used to solve this issue?

    NOTE: I want to solve this problem using SSIS and not SQL or excel.

    NOTE: The column is of datatype double precision float.


    Dhananjay Rele

    Thursday, June 27, 2013 6:17 PM
  • Did I answer your post several hours ago, didn't I?

    Arthur My Blog

    Thursday, June 27, 2013 6:27 PM
    Moderator
  • This one:

    http://social.msdn.microsoft.com/Forums/en-US/654897e7-b6b6-47c5-8997-bde9c19935c8/replace-null-with-blanks

    You can use a Derived Column Transformation with the following expression

    ISNULL([EMPLOYEE FNAME]) ? " " : [EMPLOYEE FNAME] 


    Arthur My Blog

    Thursday, June 27, 2013 6:28 PM
    Moderator
  • yeh iv left a new reply there too

    there is a column with float datatype, this one woluldnt accept the expression, the other columns which are varchar accepted the expression.

    is thher a different expression for float datatype?


    Dhananjay Rele

    Thursday, June 27, 2013 6:48 PM
  • yeh iv left a new reply there too

    there is a column with float datatype, this one woluldnt accept the expression, the other columns which are varchar accepted the expression.

    is thher a different expression for float datatype?


    Dhananjay Rele


    there is no blank value within a float... just null... you have to cast it to a string first.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, June 27, 2013 7:12 PM
    Moderator
  • Arthur,

    one of these fields was a float, and seems like this transformation works only for varchar values.

    Do we have any workaround for this?

    to get rid of nulls in a float value?


    Dhananjay Rele

    You cannot put blank for a float field. In that case you have to put a default value (say -1.0) using following expression:

    ISNULL(FloatFieldName)? -1.0 : FloatFieldName


    Nitesh Rai- Please mark the post as answered if it answers your question

    Friday, June 28, 2013 7:45 AM