conversion from string to decimal

Question

conversion from string to decimal

swatianand83

SSC Eights!

Points: 930
More actions
August 12, 2008 at 3:57 am

#123822

I have a string column and need to map it with a decimal(12,2) column in SSIS package. Have tried using derived transformation and data convertor. but it gives an error of 'value could not be converted because of potential loss of data'.
any help in this regard will be appreciated.
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

Redirect the rows from the convert and see what the row is that is complaining. Probably find......it is not convertable 🙂

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 2

There can be a few cases where the derived transformation can fail like the data can come as string instead of a number. So you can use and conditional operator for these cases and assign a null value or redirect all these rows to a error destination and check what is excatly going wrong in the data.

Then you can decide whether to insert a null value in the row or discard the rows.

Regards
Venkat
http://sqlblogging.blogspot.com[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

I'd question the database designer as to why decimal values were stored as strings to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Crispin Proctor SSCoach Points: 18161 More actions · Answer 4

Hey, i've seen a "all fields are varchar, just in case" database.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Heh... did they also happen to have all the data in a single EAV table? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)