Dealing with NaN’s in SSIS

azeempak 6 Reputation points

I have a column that's generated from a custom component on the Data Flow in SSIS. The data type of the column is float[DT_R8] that has along with valid float values, NaN's in there. I would like to identify these NaN's and treat(assign) these as NULL values. I thought of doing something in the Derived Column Transformation like in the screenshot, but this didn't work.

It seems that in the Expression column, it can only be built from the functions available. But there isn't a 'isNaN' function that can be used.

Would you know of any other approaches, or how it can be done?


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,658 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 40,656 Reputation points

    As far as I know the data type DT_R8 for float in SSIS don't support the state NaN, only real numerics and NULL.
    Is it really the type DT_R8, or maybe mor e DT_STR for string?

    Integration Services Data Types

    0 comments No comments

  2. ZoeHui-MSFT 32,506 Reputation points

    Hi @azeempak ,

    We may try to convert the [DT_R8] to [DT_WSTR] first.

    And then use Derived Column like [col name] == "NA" ? NULL(DT_WSTR,50) : [col name] to convert the nan to null.

    At last, convert the [DT_WSTR] to [DT_R8].

    A little troublesome, but it's a way I can think.



    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments No comments