Converting NVarchar to Real, without potential loss of data
Hi there, I am extracting data from SAP using SSIS. The problem I have is that one of the fields is a data type nvarchar. This must be converted to a real data type. I am using a data conversion transform to create a new column for the field with the data type real (DT_R4). When I execute the package I receive an error "value can't be converted because of potential loss of data". How can I convert this nvarchar to real without getting the error of "...potential loss of data"
Thanks
Thanks
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
I agree with Lowfatspread: REAL and FLOAT are floating-point notation numerical data types, hence stored values only unprecisely.
you should use DECIMAL or NUMERIC data type instead...
you should use DECIMAL or NUMERIC data type instead...
ASKER
The field contains numeric values like 1.00000, 6.52525.
In SQL Server I can change the table's data type, via right click>design, from nvarchar to real. I also get the warning of "...potential data loss", but I can select to continue and it converts the data type perfectly. When running the package I don't get the warning of "...potential data loss". The package just terminates, with the error "...potential data loss"
The fields length is not huge, it's nvarchar(14).
In SQL Server I can change the table's data type, via right click>design, from nvarchar to real. I also get the warning of "...potential data loss", but I can select to continue and it converts the data type perfectly. When running the package I don't get the warning of "...potential data loss". The package just terminates, with the error "...potential data loss"
The fields length is not huge, it's nvarchar(14).
>The fields length is not huge, it's nvarchar(14).
according to the data samples, we can only repeat: use DECIMAL(10,5) instead of REAL, and that error will go away.
according to the data samples, we can only repeat: use DECIMAL(10,5) instead of REAL, and that error will go away.
ASKER
I have just run the data conversion with decimal, as well as to convert to numeric. I still get the same problem "[Data Conversion [2080]] Error: Data conversion failed while converting column "WKURS" (1409) to column "Copy of WKURS" (2110). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
then you have rows with more decimals or more digits.
take the longest data for reference.
take the longest data for reference.
ie, with varchar(14), the following must work:
decimal(28,14)
decimal(28,14)
ASKER
Angel, you see I got the data from SAP, but it was quite dirty for example it had spaces and slashes in it ie. " /0.12345" so I also had to do a derived column REPLACE(TRIM([WKURS]),"/",
ASKER
I just ran it again to convert to a decimal with a scale of 14 and then with 8 since 8 was the maximum characters now/, after the derived column.
just a short note:
decimal (28,14) means total of 28 digits, of which 14 are after the comma (ie 28-14=14 digits before the comma).
decimal (28,14) means total of 28 digits, of which 14 are after the comma (ie 28-14=14 digits before the comma).
ASKER
Okay, the max i have in front of the comma is 2 and i will have 5 behind the comma. When I use the data conversion transform i can only set the scale. It's not possible to set the conversion there to decimal(28,14).
ASKER
I tried something new, in the Derived Column Transform I'm not updating the column anymore, I'm creating a new column, Derived Column=Copy of WKURS, Expression=REPLACE(TRIM([W
"[Derived Column [2428]] Error: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type."
Thanks
"[Derived Column [2428]] Error: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type."
Thanks
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Is it possible that when one of the rows contains no value at all ie. there's some rows that are " " then after the derived column it is "". Can this be a problem?
ASKER
Hi angel thanks for all the tips I got it right!
is the nvarchar defined with a huge possible length?
do the data contain leading zeros?
wouldn't a Decmal(m,p) datatype be better?
have you confirmed that the column actual contains valid data which can be converted to a real..?
use isnumeric(columnname) = 1 (ok)