none
Excel Source 'Empty' records as 'Empty' in the destination table, not as NULL - Please help!

    Question

  • Hi,
         I have a Excel Source  and  OLE DB table destination. Eg: 'Department' column in table is varchar type.
    This column is coming 'Empty' from excel and hence in the table it is populated as NULL. But I want 'Empty' values only, NOT the NULL values.

    In between Source and destination, I have a derived column where I am converting this column from Excel format DT_WSTR to DB fotmat DT_STR using the expression
    (DT_STR,150,1252)LTRIM(RTRIM([Department])). But this is to convert from WSTR to STR only. 

    Can you please help me to add the code here  to bring Empty values as Empty in the table and NOT as NULL?

    thanks!



    • Edited by vskindia Thursday, November 14, 2013 5:57 AM edit
    Thursday, November 14, 2013 12:53 AM

Answers

  • Hi,

       Thanks. In derived transformation, i already have this expression (DT_STR,150,1252)LTRIM(RTRIM([Department])) - In this, where can i add ISNULL expression?

    • Marked as answer by vskindia Monday, November 18, 2013 2:40 AM
    Thursday, November 14, 2013 10:48 AM
  • (DT_STR,150,1252)(ISNULL([Department])?"":LTRIM(RTRIM([Department])))


    • Edited by signature2008 Thursday, November 14, 2013 11:18 AM
    • Marked as answer by vskindia Monday, November 18, 2013 4:39 AM
    Thursday, November 14, 2013 11:17 AM

All replies

  • What do you mean "Blank"?  Do you mean empty string?  You could do that with an expression in a Derived Column transformation.

    ISNULL(LastName)? "":LastName
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, November 14, 2013 2:57 AM
  • Hi,

       Thanks. In derived transformation, i already have this expression (DT_STR,150,1252)LTRIM(RTRIM([Department])) - In this, where can i add ISNULL expression?

    • Marked as answer by vskindia Monday, November 18, 2013 2:40 AM
    Thursday, November 14, 2013 10:48 AM
  • (DT_STR,150,1252)(ISNULL([Department])?"":LTRIM(RTRIM([Department])))


    • Edited by signature2008 Thursday, November 14, 2013 11:18 AM
    • Marked as answer by vskindia Monday, November 18, 2013 4:39 AM
    Thursday, November 14, 2013 11:17 AM
  • Try this

    [Department] == "" || (ISNULL(LTRIM(RTRIM([Department]))) ? ' '(DT_STR,150,1252) : (DT_STR,150,1252)LTRIM(RTRIM([Department]))


    I am sorry , i can not validate that because of absence of development environment. My intention is to provide you and idea how we can do that.

    Hope this will help



    Thursday, November 14, 2013 11:25 AM