Post reply

SSIS - Data Conversion Transformation/Derived column - Varchar to Integer, Null

  • January 24, 2022 at 11:04 pm

    #3976736

    I am relatively new to SSIS and I need to convert a varchar to and integer within SSIS that will handle Null values.

    I have research numerous websites with out success.

    How do I convert a varchar to integer and still handle the if there is a null value?  I have tried both Derived Column Transformation Editor & the Data Conversion Transformation editor, however I do no see where to indicate how to handle null values.Derived Error

    DCT

    Source:

    Destination:

    As you can see from the Source file and Destination file the difference in field types.

    Error msg which points me to possible Null value issue:

    The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    Hoping you can point me in the right direction, any assistance offered is greatly appreciated.

    Thanks,

    Karen

  • January 25, 2022 at 11:10 pm

    #3977193

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • January 26, 2022 at 10:36 pm

    #3977687

    I'm bumping this for you, Karen.  Seems like it should be simple to do in SSIS but I don't use SSIS and can't help.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 26, 2022 at 10:43 pm

    #3977689

    Are you sure it's blowing up because it's null?  Might want to try a simple select * from stg.users where try_convert(int,home_location_id) is null.

    I'm sure the syntax is wrong there.

    I've run into issues like this before where it was actually a weird value in the column.  tab's etc.

     

    And side note, I've used SSIS for about 10 years and I despise transformations.  I do almost anything that requires conversion in T-SQL.

    • This reply was modified 2 years, 3 months ago by  daytonbrown2. Reason: added comment

  • January 27, 2022 at 11:19 pm

    #3978091

    Thanks for the input, found solution on another forum.

Viewing 5 posts - 1 through 4 (of 4 total)

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