none
how to get ride of "N/A" RRS feed

  • Question

  • Hi

    I am importing data from text file which has "N/A" in MOST of the column which is causing data conversion error. How to deal with this situation so that SSIS do not import data when it is "N/A" or replace "N/A" with NULL (empty) before import.

    for example

    Column1 Column2 Column3 Column4

      1              ABC         N/A        N/A

      2              N/A         XYZ        N/A

    Regards

    Wednesday, June 14, 2017 2:13 PM

Answers

  • Hi Tanv,

    You are looking for ? : (Conditional) expression.

    For example

    Column1 == "N/A" ? " " : Column1

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by A Tanv Friday, October 11, 2019 7:33 PM
    Wednesday, June 14, 2017 4:04 PM
    Moderator

All replies

  • Import these columns as text, then using the Derived Column Transformation override / replace the 'N/A' values with (DB_NULL)(A_Column) SSIS expression

    Arthur

    MyBlog


    Twitter

    Wednesday, June 14, 2017 2:30 PM
    Moderator
  • Hi Tanv,

    You are looking for ? : (Conditional) expression.

    For example

    Column1 == "N/A" ? " " : Column1

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by A Tanv Friday, October 11, 2019 7:33 PM
    Wednesday, June 14, 2017 4:04 PM
    Moderator
  • Thank you Zhang,

    what if column is time data type, for example values are:

    4:00PM

    N/A

    6:00PM

    N/A

    Regards

    Wednesday, June 14, 2017 6:21 PM
  • Replace with the expression given above.

    Just a time is odd, depends on the business case what needs to be done.


    Arthur

    MyBlog


    Twitter

    Wednesday, June 14, 2017 7:19 PM
    Moderator