SQL Server nvarchar data issue

Question

I am working on SSIS package and replace NULL to NaN using zappysys Export csv component and it is working fine.
But in one case where we have column data type Nvarchar and length 10 having space in the table is converting the data into 'NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN' , it is converted into 10 times NaN.
After this i checked the data in source server and it is having space only with no data as below:

but when i check the length it is having 10 character length and i tried to add two * before and after the value but that is also not happening. But in the same place if i am doing for Fax column i am getting two **.
Kindly help if anyone know what type of data it is so that i will replace that with some other value while converting in ETL/SSIS.

Thanks
Azhar Khan

Answer 1

This is a little confusing, you say that you replace NULL with NaN, but in the screenshot you have non-NULL values?

In any case, we don't see the code where you put in the NaN. So this is all a bit of a guessing game. But add datalength(Fax) and datalength(SupplierLevel) to your debug output. datalength returns the number of bytes in the value. len, on the other hand, returns the number of characters, excluding trailing spaces, which can trip you.

Answer 2

Hi ErlandSommarskog.....

I have replaced the Null with NaN in Zappysys component as below:

and we are getting the values as 10 times NaN in csv file as below:

Answer 3

In very first screenshot the values for supplierLevel column is blank but the length is 10 and when I copied the same value and paste in notepad it is having blank value like '' (when I copied the value from the table it is nothing as between two inverted comma) . So i need to understand what is that value present in the database.

Answer 4

Replacing a single char (NULL) with 3 chars ("NaN") is going to be problematic. You should probably just replace NULL with a single space or nothing instead, during your export.

Answer 5

Hi @Azhar Khan ,

The data conversion is happening in zappysys, which is an area we are not familiar with, you can also ask then for help:
https://zappysys.com/support/

if the column value is the string 'NaN', it must be varchar, so you can try to choose other char, like '0' to see if also have the same error

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.