none
How to convert blank into null in ssis RRS feed

  • Question

  • I am extracting data from excel in ssis. One of the excel column contain blank values. so i need to replace balck value with null otherwise my package is going fail.
    Any suggessions?

    pulikondas

    • Moved by Elvis LongModerator Monday, October 29, 2012 9:51 AM better support (From:SQL Server Analysis Services)
    Wednesday, October 24, 2012 4:13 PM

Answers

All replies

  • Use a Derived Column component to test if the length of the value is 0 if string or the value is 0 if numeric and replace with NULL if so. Should be moved to the SQL Server Integration Services forum.

    http://martinmason.wordpress.com


    • Edited by Martin Mason Wednesday, October 24, 2012 4:35 PM
    Wednesday, October 24, 2012 4:34 PM
  • Hi,

    try this after import the excel to a stage table:

    DECLARE @TEST VARCHAR
    
    SET @TEST = ''
    
    SELECT 
    	@TEST AS BLANK
    	,CASE WHEN @TEST = '' THEN NULL ELSE @TEST END AS 'NULL'


    "Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta." Érica Tohoma | http://bloggirlsa.wordpress.com

    Wednesday, October 24, 2012 6:05 PM
  • Hi Dipsp,

    You can use following expression in Derived Column Transformation to convert blank into null,

    TRIM(<YourColumnName>) == "" ? (DT_STR,4,1252)NULL(DT_STR,4,1252) : <YourColumnName>

    For more information about handling NULL or implied NULL values in an SSIS Derived Column, please see:
    http://www.bidn.com/blogs/DonnyJohns/ssas/1919/handling-null-or-implied-null-values-in-an-ssis-derived-column

    Thanks,
    Eileen


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Eileen Zhao Monday, November 5, 2012 1:26 AM
    Tuesday, October 30, 2012 3:03 AM
  • Hi Dipsp,

    Elieen solution is aboslutely right but i want to put some light on the NULL Conversion which required conversion on both side but normally conersion si required on the left side only.but for NULL which is special type of conversion which required conversion on the right hand side.

    (DT_STR,8,1252)(TRIM( @[System::CreatorName]) == "" ?  (DT_STR,8,1252)NULL(DT_STR,8,1252)  :  @[System::CreatorName]  )

    Thanks,

    Zaim Raza.

    Tuesday, October 30, 2012 3:49 AM
  • Hey,

    You can use DerivedColumn.

    Inside, select to replace the Column in the pipe that u got from the Excel file.

    In the expression you can write :

    1. if your table column destination is NON-UNICODE  write : ISNULL(TRIM(<your column name>)) ? (DT_STR, ##, <code page>)NULL : <your column name>          

    2. if your table column destination is UNICODE  write : ISNULL(TRIM(<your column name>)) ? (DT_WSTR, ##)NULL : <your column name>          

    Note :

    1. if you want anther convertion for you original data to unicode/non-unicode just add convertion before <your column name>.

    2. the max length of data in a single excel cell is aprox 255 chars (i think...) so you can convert with max limit.

    Tuesday, October 30, 2012 7:48 AM