Post reply

Set fields with Null values to blank

  • August 18, 2009 at 7:49 am

    #137327

    Hi all,

    I am a newbie to SSIS, I need some help here.

    I have an SSIS package which does basic copying. What we want to do here is set the fields in the destination column having Null values to Blank.

    Since these are field delimited files this means that each field will need corresponding blanks (ex: field size 5 = ‘ ‘ and not just ‘’).

    Thank you,

    S

  • August 18, 2009 at 8:03 am

    #1040881

    If I am reading this right, from your source, you have columns that are coming across as NULL that you want translated to blank before entering into your destination.

    To do this, add in a Derived Column Transformation between your source and destination steps in the Data Flow task. The logic, which is placed in the Expression column for the new column, will be something like the following:

    ISNULL( [ColA] ) ? ' ' : [ColA]

    Give this column a different name and then in your Destination task, use the New Derived Column as the source instead of the original column.

    HTH

  • August 18, 2009 at 12:50 pm

    #1041103

    If your source is SQL Server might I suggest you do the conversion in your SQL Statement in the source. Something like:

    DECLARE @test-2 TABLE (col VARCHAR(5))

    INSERT INTO

    @test-2 (col)

    SELECT

    '12345'

    UNION

    SELECT

    NULL

    Select

    IsNull(col, Replicate(' ', 5)) AS REPLICATED,

    CONVERT(CHAR(5), ISNULL(col, ' ')) AS converted,

    col AS is_Null

    FROM

    @test-2

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 3 posts - 1 through 2 (of 2 total)

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