Dealing with NaN’s in SSIS

Question

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?

Thanks!

Answer 1

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

Answer 2

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.

Regards，

Zoe

---

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